×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

need a store proc sample for this problem

need a store proc sample for this problem

need a store proc sample for this problem

(OP)
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.

RE: need a store proc sample for this problem

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

CODE

select ID, Column1, Value1 from leTable where Column1 = 'State'
, and then bring these all together using left joins.


ie:

CODE

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:

ID     STATE     CITY     DEPT   EMP_COUNT     ETC...
1      CA        LA       HR       50            ETC...


It would make your life much easier.

Hope this helps,

Alex

----signature below----
I'm pushing an elephant up the stairs

My Crummy Web Page

RE: need a store proc sample for this problem

(OP)
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?

RE: need a store proc sample for this problem

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

RE: need a store proc sample for this problem

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

----signature below----
I'm pushing an elephant up the stairs

My Crummy Web Page

RE: need a store proc sample for this problem

Ok yehong, here's an example.

CODE

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

----signature below----
I'm pushing an elephant up the stairs

My Crummy Web Page

RE: need a store proc sample for this problem

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close