January 12, 2011 at 12:15 pm
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.
January 12, 2011 at 12:26 pm
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.
January 12, 2011 at 12:26 pm
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
January 12, 2011 at 12:54 pm
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!
January 12, 2011 at 1:51 pm
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
January 12, 2011 at 1:54 pm
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
January 12, 2011 at 2:03 pm
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.
January 12, 2011 at 2:52 pm
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[/url] covering the comma separated list issue...
January 13, 2011 at 12:44 am
😛 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
January 13, 2011 at 12:48 am
sorry my English is poor,I'm trying:-D
January 13, 2011 at 9:32 am
Jon.Wales (1/13/2011)
sorry my English is poor,I'm trying:-D
Do you have any question? You posted some code. Why?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply