can PIVOT command transpose rows into columns

Question

can PIVOT command transpose rows into columns

Karthika Raman

SSCrazy

Points: 2520
More actions
February 5, 2008 at 11:49 am

#378347

Hi,
I just need to flip row data into column data but the number of columns is not predetermined. There is no aggregation, but just transposing of row to columns
Here is the example of data.
MemberNumber - unique number for the member
famNumber - unique for the family
depcode - indicates the dependent status
MemberNumber famNumber depcode FirstName LastName
12345 23 e john smith
23456 23 s joe smith
34567 23 1 kara smith
45678 24 e jane doe
56789 24 s john doe
I would like the output be:
famNumber (depcode FirstName Last Name - these columns would be repeated for as many dependents that the family has) all the data for a family should be populated in a single row.
Thanks in advance
KR

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Does it need to be columns, or would a list (with commas or some such) work?

Is there a reason to have SQL do this, and not to have the front end or reporting application do it? (Usually, reporting applications are better at dynamic pivots than SQL. For example, pivot tables in Excel are great for this kind of thing.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

If the family+depcode generates duplicates, then no- PIVOT will aggregate them out. You'd also have to concatenate the first and last names together, since PIVot will only do one "column" for pivot operation.

With that said - with enough torturing of the data - you CAN probably get it to PIVOT correctly.

create table names (famid int, name varchar(30), relcode char(3))

insert names

select 1,'bob','e' UNION ALL

select 1,'jane','s' UNION ALL

select 1, 'little joey','1' union all

select 1,'little janey','1'

go

select * from

(

select rtrim(relcode)+

ltrim(case when relcode='1'

then cast(ROW_NUMBER() over (partition by famid,relcode

ORDER by famid,relcode)

as varchar)

else '' end) as new_rel,

famid,name from names

) topvt

PIVOT

(max(name) FOR new_rel in ([e],,[11],[12],[13],[14])) pvt

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Karthika Raman SSCrazy Points: 2520 More actions · Answer 3

Actually, it does not have to be in sql, but it has to be something that is automated, not what a user would need to do. Could you build something like this in report builder for SQL server reporting services?

Thanks

KR

GSquared SSC Guru Points: 260824 More actions · Answer 4

Yes, Reporting Services could do this, with a cross-tab report.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Even in a report, CrossTab represents a summarization or aggregation operation, so you would STILL have to fix the duplication issue (i.e. if all children were marked as relcode=1, only ONE would be shown.).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Karthika Raman SSCrazy Points: 2520 More actions · Answer 6

Technically family + depcode should not create duplicates - i.e. all children are not marked with 1.

However due to bad data entry there could be two spouses, so we may have to deal with that.

Thanks

KR

nagarajanmm-699934 SSC-Addicted Points: 421 More actions · Answer 7

Hi,

I need to convert rows into columns for the following table values.

Attribute_ID Attr_Value

1 'Test'

2 'Succeeded'

3 'Passed'

I need a output like the following

Attr1_ID Attr1_value Attr2_ID Attr2_Value Attr3_ID Attr3_Value

1 'Test' 2 'Succeeded' 3 'Passed'

I tried using Pivot, but could not get the desired output. Can someone help me on this?

Thanks in advance.

Regards,

Nags

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

nagarajanmm (5/6/2008)
Hi,
I need to convert rows into columns for the following table values.
Attribute_ID Attr_Value
1 'Test'
2 'Succeeded'
3 'Passed'
I need a output like the following
Attr1_ID Attr1_value Attr2_ID Attr2_Value Attr3_ID Attr3_Value
1 'Test' 2 'Succeeded' 3 'Passed'
I tried using Pivot, but could not get the desired output. Can someone help me on this?
Thanks in advance.
Regards,
Nags

PIvoting/cross-tabs are a grouping operation, so you need something to group on in order to get the right output. Can you give us some more specifics so as to come up with something useable?

considering you're doing some things that PIVOT won't support directly - you'd want to investigate the "old" syntax for doing this type of query. This article has a bunch of links to examples, so it's a decent place to start:

http://www.mssqltips.com/tip.asp?tip=937

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

nagarajanmm-699934 SSC-Addicted Points: 421 More actions · Answer 9

Hi,

Thanks for your reply. I understand the Pivot is for generating grouping/ aggregating reports. I will check the link that you have provided.

I am planning to use Multi-Tenant - Fixed columns architecture for storing the Attributes data which will vary between Projects. I will get the Attributes input as Rows (OpenXML string to Rows), which I have to convert into columns to insert into Multi-Tenant architecture table.

Hope you can understand my requirement.

Thanks,

Nagarajan Mohan