Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Pivot with dynamic number of columns Expand / Collapse
Author
Message
Posted Wednesday, January 12, 2011 12:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 06, 2014 11:00 AM
Points: 121, Visits: 306
I've never used the PIVOT feature in SQL 2005 and I've been reading a lot about it. I'm not sure if it's appropriate for my situation. I'm not attempting to aggregate any data. I merely want to display the data in a single row with multiple columns rather than multiple rows.
Here is an example:

CREATE TABLE [dbo].[RELATIONSHIP]
([PEOPLE_CODE_ID] [varchar](10) NOT NULL,
[NAME] varchar(255) NOT NULL,
[RECORD_NUM] INT identity)


INSERT RELATIONSHIP
(people_code_Id, [name])
values
('P000053505','Bethany')
INSERT RELATIONSHIP
(people_code_Id, [name])
values
('P000053505','Aaron')
INSERT RELATIONSHIP
(people_code_Id, [name])
values
('P000053505','Benjamin')
INSERT RELATIONSHIP
(people_code_Id, [name])
values
('P000053505','Nathanael')
INSERT RELATIONSHIP
(people_code_Id, [name])
values
('P000053505','Ethan')
INSERT RELATIONSHIP
(people_code_Id, [name])
values
('P000053505','Rachel')
INSERT RELATIONSHIP
(people_code_Id, [name])
values
('P000053505','Joshua')
INSERT RELATIONSHIP
(people_code_Id, [name])
values
('P000053505','Joanna')
INSERT RELATIONSHIP
(people_code_Id, [name])
values
('P000053505','Rebekah')


I really need it to be in the format
People_Code_ID, Child1, Child2, Child3, Child4, Child5, Child6, Child7, Child8, Child9

Also, the dynamic portion is that each people_code_ID could have a different number of children.
Post #1046710
Posted Wednesday, January 12, 2011 12:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 6,908, Visits: 12,625
Instead of using PIVOT I'd rather go with CrossTab and DynamicCrossTab, since the code structure of PIVOT is kinda weird and it doesn't perform better than the CrossTab methods either.

I'd recommend to take a look at the CrossTab method referenced in my signature. Once you're familiar how it works you should read the DynamicCrossTab article.

As a side note: I've seen versions for dynamic Pivot on the web, too. And both will require using dynamic SQL.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1046722
Posted Wednesday, January 12, 2011 12:26 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
Do you want different columns, or do you want a comma-delimited list?

You can do a dynamic pivot through dynamic SQL of various sorts. Simple-Talk (www.simple-talk.com) has a good article on it, by Phil Factor.

You can generate a list by an XML query.


- 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
Post #1046723
Posted Wednesday, January 12, 2011 12:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 06, 2014 11:00 AM
Points: 121, Visits: 306
LutzM, I just read your articles on crosstabs. I am sure I'm just being thick-headed, but I cannot wrap my brain around how it applies to my situation because I don't have any data to aggregate. I'm not trying to sum anything. I just want to re-arrange existing data.

I also found and read the Simple-Talk article. I think I'm closer to understanding, but again, I'm thrown by the fact that all these scenarios have something to sum and I don't have that.

I need to create a view with separate columns. The business case is that we have an application with an extremely limited reporting module. I'm hoping to create a custom view the user can join to on people_code_Id that will already have the childname columns listed out in a single row by people_code_Id.

I know the goal of this forum is to provide tools and get readers to do the thinking for themselves. I really am trying. It's not a case of laziness here. Could somebody just write the pivot or cross-tab statement based on the data in my table example above? If I can just see it, I think I can handle the dynamic piece. Thanks in advance!
Post #1046744
Posted Wednesday, January 12, 2011 1:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 6,908, Visits: 12,625
I'm thrown by the fact that all these scenarios have something to sum and I don't have that.


Well, you DO have something to aggregate...
Attached please find tow code snippets:
the first one is without any aggregation. As you'll see, you end up with the same number of rows like the original table, each row holding just one value beside the people_code_id.

But you only want to have one row per people_code_id. Therefore, you'd need to aggregate your values.
With text data, you usually use MAX(), as shown in the second code snippet.

I agree, it's easier to "get the concept" if you see data based on your original sample. So I wrote the first part of the CrossTab query you'll need.

SELECT  
people_code_id,
CASE WHEN RECORD_NUM = 1 THEN NAME ELSE NULL END AS CHILD1,
CASE WHEN RECORD_NUM = 2 THEN NAME ELSE NULL END AS CHILD2
FROM RELATIONSHIP

SELECT
people_code_id,
max(CASE WHEN RECORD_NUM = 1 THEN NAME ELSE NULL END) AS CHILD1,
max(CASE WHEN RECORD_NUM = 2 THEN NAME ELSE NULL END) AS CHILD2
FROM RELATIONSHIP
GROUP BY people_code_id





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1046804
Posted Wednesday, January 12, 2011 1:54 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
Here's what I came up with:

DECLARE @Cmd VARCHAR(MAX);

SELECT @Cmd = 'select ' + STUFF((SELECT ',(select [name] '
+ ' from RELATIONSHIP where Record_Num = ' + CAST(Record_Num AS VARCHAR(10)) + ') as Child' + CAST(ROW_NUMBER() OVER (PARTITION BY People_Code_ID ORDER BY Record_Num) AS VARCHAR(10))
FROM RELATIONSHIP
ORDER BY Record_Num
FOR XML PATH(''), TYPE).value('./text()[1]','varchar(max)'), 1,1,'');

EXEC (@Cmd);

Does that get you started?


- 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
Post #1046808
Posted Wednesday, January 12, 2011 2:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 06, 2014 11:00 AM
Points: 121, Visits: 306
Awesome! Yes, both of these help me tremendously! Thank you so much!

I've been wanting to learn about the STUFF syntax, too. I'm going to dive into both of these examples and figure this out.

I really appreciate that you took the time to use my example. It's beginning to click now.
Post #1046817
Posted Wednesday, January 12, 2011 2:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 6,908, Visits: 12,625
lduvall (1/12/2011)
Awesome! Yes, both of these help me tremendously! Thank you so much!

I've been wanting to learn about the STUFF syntax, too. I'm going to dive into both of these examples and figure this out.

I really appreciate that you took the time to use my example. It's beginning to click now.


Excellent!!
There are people simply copying the code and be happy a single issue has been resolved.
And there are people like you who just need a few lines of code for a general concept to "click". The latter makes it all worth the effort...

Edit: you might want to have a look at this article covering the comma separated list issue...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1046844
Posted Thursday, January 13, 2011 12:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 13, 2011 5:48 PM
Points: 2, Visits: 12
select 'oh! '+ char('115')+char('104')+char('105')+char('116')

--an exampl
--①create test table
create table test(indust varchar(10)
,[200301] varchar(10)
,[200302] varchar(10)
,[200303] varchar(10))
insert test select 'a','111','222','333'
union all select 'b','444','555','666'
union all select 'c','777','888','999'
union all select 'd','789','910','012'
go

--②table information
select * from test
/*
indust 200301 200302 200303
---------- ---------- ---------- ----------
a 111 222 333
b 444 555 666
c 777 888 999
d 789 910 012

(4 affected)
*/

--③process
declare @f1 varchar(8000),@f2 varchar(8000),@f3 varchar(8000)
select @f1='',@f2='',@f3=''
select @f1=@f1+',['+indust+']='''+[200301]+''''
,@f2=@f2+','''+[200302]+''''
,@f3=@f3+','''+[200303]+''''
from test
exec('select 日期=''200301'''+@f1
+' union all select ''200302'''+@f2
+' union all select ''200303'''+@f3)
go

/*--result
日期 a b c d
------ ---- ---- ---- ----
200301 111 444 777 789
200302 222 555 888 910
200303 333 666 999 012

(3 affected)
--*/

--④drop test table
drop table test

Post #1047040
Posted Thursday, January 13, 2011 12:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 13, 2011 5:48 PM
Points: 2, Visits: 12
sorry my English is poor,I'm trying
Post #1047042
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse