need a store proc sample for this problem 1

[yehong]

Hi, I have a table whose data is in the following format:

ID Column1 Value1
1 State California
1 City Los Angeles
1 Dept HR
1 EmpCount 50
2 State New York
2 City New York
2 Dept Accounting
2 EmpCount 30
2 SubDept Accounting1
...
The ID can have an n number of column1 and value1. I need to write a store proc that would loop through it and bring me back a result set like:
ID State City Dept EmpCount ...
1 California Los Angeles HR 50
2 New York New York Accounting 30 Accounting1...

I would really appreciate if someone can provide some sample code. Thanks in advance.

 

[AlexCuse]

Basically, for each attribute you want to show in your result you will need to write a query like

select ID, Column1, Value1 from leTable where Column1 = 'State'

, and then bring these all together using left joins.


ie:

select st.id
	, st.Value1 as State
	, cty.Value1 as City
	, dpt.Value1 as Dept
	, emp.Value1 as EmpCount
from (
	select ID, column1, value1 from leTable where Column1 = 'State'
) st left join (
	select ID, column1, value1 from leTable where Column1 = 'City'
) cty on st.id = cty.id
left join (
	select ID, column1, value1 from leTable where Column1 = 'Dept'
) dpt on st.id = dpt.id
left join (
	select ID, column1, value1 from leTable where Column1 = 'EmpCount'
) emp on st.id = emp.id

Read up on proper database design, if you don't want this to happen in the future. In fact, I would go so far as to suggest you redesign this table in a more queryable fashion, ie:

[tt]ID STATE CITY DEPT EMP_COUNT ETC...
1 CA LA HR 50 ETC...[/tt]

It would make your life much easier.

Hope this helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[yehong]

Thanks for your response, Alex. The problem is that the table is designed in that way. And the number of rows for each id could be numerous. So, there needs be a dynamic code that gets a single id and loops through the table for all of its rows and creates a one horizontal row. I can do it through a query by hardcoding values like 'State', 'Dept', but in most cases we won't know the column1 values hence we can't use hard coded values. Any other ideas please?

 

[djj55]

How about distinct query and update? Create a table with ID then update based on the unique values from Column1?
Just an idea.
djj

 

[AlexCuse]

I think you'll need to use dynamic SQL. When I get a minute today I'll throw together an example.

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[AlexCuse]

Ok yehong, here's an example.

create table #temp (n int, col1 varchar(30), val1 varchar(30))

insert #temp
select 1, 'Alex', 'Blah'
union all select 1, 'Yehong', 'Bluh'
union all select 2, 'SQL', 'Ick'
union all select 2, 'Banana', 'Yum'
union all select 2, 'Coffee', 'Cold'
union all select 3, 'CompactDisc', 'Laser'
union all select 3, 'Alex', 'Worst'
union all select 3, 'select', 'Why'


declare @q table (id int identity(1,1), col1 varchar(30))

insert @q
select distinct col1 from #temp


declare @sql nvarchar(4000)

set @sql = 'select n'

declare @x int
set @x = 1

declare @foo varchar(30)

while @x <= (select max(id) from  @q)
begin
	set @foo = (select col1 from @q where id = @x)
	set @sql = @sql + ', max(case when col1 = ''' + @foo + ''' then val1 else null end) as [' + @foo + ']'
	
	set @x = @x + 1
end

set @sql = @sql + ' from #temp group by n'

execute sp_executesql @sql

drop table #temp

You'll only be able to get up to a 4000 character SQL Statement, so you may ene up being limited by that.

Make sure you use SET NOCOUNT ON in your proc, otherwise it will really screw with the DTS (the first message generated by the proc is what DTS picks up).

I still suggest you redesign this table while you still can.

Hope this helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[yehong]

Hi, Alex. really appreciated. i will give it a try and let you guys know the results.