changing data from vertical to horizontal

I have data in a vertical format, that I want to un-normalize into a
Horizontal (single-row-per-person) format to make it easier for users to
query on. (data/queries abbreviated for the sake of efficiency and not
boring the heckout of anyone reading):



The metadata table:

attribute_id description import_key select_mask
sort_order

-11 Not applying noessay -1 -14

-8 P essay received Precd -1 -8

-7 P essay q sent Psent -1 -7

-6 P Invite Pinvite -1 -6

-5 P Deny Pdeny -1 -5

-4 P Recipient Precipient -1 -4

-3 P Offer Poffer -1 -3

-2 P Waitlist Pwaitlist -1 -2

-1 P Hold Phold -1 -1



The applicant Data table

applicant_user_id attribute_id

182182 -11

182387 -11

182388 -8

182388 -7

182463 -10

182463 -8

182463 -7

182645 -12

182645 -8

182645 -7



Desired output



applicant_user_id Not applying P essay received P
essay q sent P Invite P Deny P Recipient P Offer P
Waitlist P Hold

182182 1 0 0 0 0 0
0 0 0

182387 1 0 0 0 0 0
0 0 0

182388 0 1 0 0 0 0
0 0 0

182388 0 0 1 0 0 0
0 0 0

182463 0 0 0 0 0 0
0 0 0

182463 0 1 0 0 0 0
0 0 0

182463 0 0 1 0 0 0
0 0 0

182645 0 0 0 0 0 0
0 0 0

182645 0 1 0 0 0 0
0 0 0

182645 0 0 1 0 0 0
0 0 0





I can do this using this query style (abbreviated for sake efficiency):

Select applicant_user_id,

'Not applying' =

CASE

WHEN [Attribute_id]='-11' THEN 1

ELSE 0

END,

'P essay received' =

CASE

WHEN [Attribute_id]='-8' THEN 1

ELSE 0

END,

'P essay q sent' =

...

FROM Applicant_Attributes

WHERE applicant_user_id in
('182182','182387','182388','182388','182463','182463','182463','182645','182645','182645')





But, ideally, I'd like an automated way to do this so I can tuck it into a
sp. I can do this in ColdFusion or ASP (select * from General_Attributes,
then loop over to build the query string). I'd like to do this so when the
values change in the table, I don't have to rewrite the query each time.



So. any ideas or resources? Is it even feasible in just T-SQL?



TIA,



Chris

if you are using SQL Server 2005, you can use the PIVOT operator. Check it
in Books OnLine, you have examples there as well.

--
Dejan Sarka
http://blogs.solidq.com/EN/dsarka/default.aspx

"cb" wrote in message

>
>
> I have data in a vertical format, that I want to un-normalize into a
> Horizontal (single-row-per-person) format to make it easier for users to
> query on. (data/queries abbreviated for the sake of efficiency and not
> boring the heckout of anyone reading):
>
>
>
> The metadata table:
>
> attribute_id description import_key select_mask
> sort_order
>
> -11 Not applying noessay -1 -14
>
> -8 P essay received Precd -1 -8
>
> -7 P essay q sent Psent -1 -7
>
> -6 P Invite Pinvite -1 -6
>
> -5 P Deny Pdeny -1 -5
>
> -4 P Recipient Precipient -1 -4
>
> -3 P Offer Poffer -1 -3
>
> -2 P Waitlist Pwaitlist -1 -2
>
> -1 P Hold Phold -1 -1
>
>
>
> The applicant Data table
>
> applicant_user_id attribute_id
>
> 182182 -11
>
> 182387 -11
>
> 182388 -8
>
> 182388 -7
>
> 182463 -10
>
> 182463 -8
>
> 182463 -7
>
> 182645 -12
>
> 182645 -8
>
> 182645 -7
>
>
>
> Desired output
>
>
>
> applicant_user_id Not applying P essay received P
> essay q sent P Invite P Deny P Recipient P Offer P
> Waitlist P Hold
>
> 182182 1 0 0 0 0 0
> 0 0 0
>
> 182387 1 0 0 0 0 0
> 0 0 0
>
> 182388 0 1 0 0 0 0
> 0 0 0
>
> 182388 0 0 1 0 0 0
> 0 0 0
>
> 182463 0 0 0 0 0 0
> 0 0 0
>
> 182463 0 1 0 0 0 0
> 0 0 0
>
> 182463 0 0 1 0 0 0
> 0 0 0
>
> 182645 0 0 0 0 0 0
> 0 0 0
>
> 182645 0 1 0 0 0 0
> 0 0 0
>
> 182645 0 0 1 0 0 0
> 0 0 0
>
>
>
>
>
> I can do this using this query style (abbreviated for sake efficiency):
>
> Select applicant_user_id,
>
> 'Not applying' =
>
> CASE
>
> WHEN [Attribute_id]='-11' THEN 1
>
> ELSE 0
>
> END,
>
> 'P essay received' =
>
> CASE
>
> WHEN [Attribute_id]='-8' THEN 1
>
> ELSE 0
>
> END,
>
> 'P essay q sent' =
>
> ..
>
> FROM Applicant_Attributes
>
> WHERE applicant_user_id in
> ('182182','182387','182388','182388','182463','182463','182463','182645','182645','182645')
>
>
>
>
>
> But, ideally, I'd like an automated way to do this so I can tuck it into a
> sp. I can do this in ColdFusion or ASP (select * from General_Attributes,
> then loop over to build the query string). I'd like to do this so when the
> values change in the table, I don't have to rewrite the query each time.
>
>
>
> So. any ideas or resources? Is it even feasible in just T-SQL?
>
>
>
> TIA,
>
>
>
> Chris
>
>