Mauro Cazabonnet
asked on
SQL Convert rows to columns
Hi,
Trying to convert the following
to look like this
following query kinda of works returns only 1 value back
Trying to convert the following
NAME StringValue
firstname IHAccount1
initials IH1
lastname Golden1
samaccountname IGolden1
jobtitle Admin
telephone 212-123-4567
messaging 1
firstname IHAccount2
initials IH2
lastname Golden2
samaccountname IGolden2
jobtitle Admin
telephone 212-123-4567
messaging 0
firstname IHAccount3
initials IH3
lastname Golden3
samaccountname IGolden3
jobtitle Admin
telephone 212-123-4567
messaging 0
to look like this
firstname initials lastname samaccountname jobtitle telephone messaging
IHAccount1 IH1 Golden1 IGolden1 Admin 212-123-4567 1
IHAccount2 IH2 Golden2 IGolden2 Admin 212-123-4567 0
IHAccount3 IH3 Golden3 IGolden3 Admin 212-123-4567 0
following query kinda of works returns only 1 value back
SELECT firstname, initials, lastname, samaccountname, jobtitle, telephone, messaging
FROM
(
select value, columnname
from @tmpUsers
) d
pivot
(
max(value)
for columnname in (firstname, initials, lastname, samaccountname, jobtitle, telephone, messaging)
) piv;
ASKER
the data will run sequentially in a linear fashion
firstname thru messaging then start over again
firstname thru messaging then start over again
ASKER
I can provide an id from the json feed
parent_ID NAME StringValue
1 firstname IHAccount1
1 initials IH1
1 lastname Golden1
1 samaccountname IGolden1
1 jobtitle Admin
1 telephone 212-531-5877
1 messaging 1
2 firstname IHAccount2
2 initials IH2
2 lastname Golden2
2 samaccountname IGolden2
2 jobtitle Admin
2 telephone 212-531-5877
2 messaging 0
3 firstname IHAccount3
3 initials IH3
3 lastname Golden3
3 samaccountname IGolden3
3 jobtitle Admin
3 telephone 212-531-5877
3 messaging 0
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thx!!!!
How do you know telephone 212-123-4567 goes with IHAccount1?