October 25, 2012 at 8:29 am
Hi Guys,
Id like to covert output from columns to rows.
Currently Selecting * from a table returns.
ACCOUNTNO Skill
C1 .NET
C1 HTML
C1 CSS
C2 .NET
C2 CSS
C2 XML
C3 CSS
C3 .NET
C3 XML
Im looking to return:
ACCOUNTNO Skill1, Skill2, Skill3
C1 .NET CSS HTML
C2 .NET CSS XML
C3 .NET CSS XML
I've spent most of the day trying to get pivots to work but Im not having much luck. I've read through the pivot articles, but cant figure out where Im going wrong.
Any help would be appreciated.
CREATE TABLE #Skills
(
ACCOUNTNO VARCHAR(5),
Skill VARCHAR(50)
)
INSERT INTO #Skills (ACCOUNTNO, SKILL)
SELECT 'C1', '.NET' UNION ALL
SELECT 'C1', 'HTML' UNION ALL
SELECT 'C1', 'CSS' UNION ALL
SELECT 'C2', '.NET' UNION ALL
SELECT 'C2', 'CSS' UNION ALL
SELECT 'C2', 'XML' UNION ALL
SELECT 'C3', 'CSS' UNION ALL
SELECT 'C3', '.NET' UNION ALL
SELECT 'C3', 'XML' UNION ALL
October 25, 2012 at 8:37 am
SELECT ACCOUNTNO,
MAX(CASE WHEN rn = 1 THEN SKILL END) AS Skill1,
MAX(CASE WHEN rn = 2 THEN SKILL END) AS Skill2,
MAX(CASE WHEN rn = 3 THEN SKILL END) AS Skill3
FROM (SELECT ACCOUNTNO, SKILL,
ROW_NUMBER() OVER(PARTITION BY ACCOUNTNO ORDER BY SKILL)
FROM #Skills
)a(ACCOUNTNO, SKILL, rn)
GROUP BY ACCOUNTNO;
or
SELECT ACCOUNTNO, [1] AS Skill1, [2] AS Skill2, [3] AS Skill3
FROM (SELECT ACCOUNTNO, SKILL,
ROW_NUMBER() OVER (PARTITION BY ACCOUNTNO ORDER BY SKILL)
FROM #Skills
) a(ACCOUNTNO, SKILL, rn)
PIVOT(MAX(SKILL) FOR rn IN ([1], [2], [3])) p;
or
SELECT ACCOUNTNO, MAX(Skill1) AS Skill1, MAX(Skill2) AS Skill2,
MAX(Skill3) AS Skill3
FROM (SELECT ACCOUNTNO, SKILL,
ROW_NUMBER() OVER (PARTITION BY ACCOUNTNO ORDER BY SKILL)
FROM #Skills) a(ACCOUNTNO, SKILL, rn)
OUTER APPLY (SELECT a.SKILL WHERE a.rn = 1) b(Skill1)
OUTER APPLY (SELECT a.SKILL WHERE a.rn = 2) c(Skill2)
OUTER APPLY (SELECT a.SKILL WHERE a.rn = 3) d(Skill3)
GROUP BY ACCOUNTNO;
Have a read through Cross Tabs and Pivots part 1[/url] and Cross Tabs and Pivots part 2[/url] by Jeff Moden if you need further help.
October 26, 2012 at 12:00 am
Hmmm but what if Skills are in different numbers for each ACCOUNTNO
Like
declare @Skills TABLE
(
ACCOUNTNO VARCHAR(5),
Skill VARCHAR(50)
)
INSERT INTO @Skills (ACCOUNTNO, SKILL)
SELECT 'C1', '.NET' UNION ALL
SELECT 'C1', 'HTML' UNION ALL
SELECT 'C1', 'CSS' UNION ALL
SELECT 'C1', 'JAVA' UNION ALL
SELECT 'C2', '.NET' UNION ALL
SELECT 'C2', 'CSS' UNION ALL
SELECT 'C3', 'CSS'
October 26, 2012 at 5:22 am
yeshupandit_2002 (10/26/2012)
Hmmm but what if Skills are in different numbers for each ACCOUNTNOLike
declare @Skills TABLE
(
ACCOUNTNO VARCHAR(5),
Skill VARCHAR(50)
)
INSERT INTO @Skills (ACCOUNTNO, SKILL)
SELECT 'C1', '.NET' UNION ALL
SELECT 'C1', 'HTML' UNION ALL
SELECT 'C1', 'CSS' UNION ALL
SELECT 'C1', 'JAVA' UNION ALL
SELECT 'C2', '.NET' UNION ALL
SELECT 'C2', 'CSS' UNION ALL
SELECT 'C3', 'CSS'
Are you talking about having a dynamic number of "skills" ?
If so, take a look at this part of my first reply: -
Cadavre (10/25/2012)
Have a read through Cross Tabs and Pivots part 1[/url] and Cross Tabs and Pivots part 2[/url] by Jeff Moden if you need further help.
Then read through part 2 of Cross Tabs and Pivots by Jeff Moden. He explains how to do what you want and includes easy to follow examples.
October 27, 2012 at 11:55 am
Cadavre (10/25/2012)
Have a read through Cross Tabs and Pivots part 1[/url] and Cross Tabs and Pivots part 2[/url] by Jeff Moden if you need further help.
Hi Cadavre,
Your first example is perfect, thank you.
I had read through those examples, but spent most of the morning trying to apply them to my needs.
Your second example has cleared up some of my confusion regarding pivots.
Thanks again. :o)
October 27, 2012 at 11:59 am
yeshupandit_2002 (10/26/2012)
Hmmm but what if Skills are in different numbers for each ACCOUNTNO
Hi yeshupandit,
The contacts I have to report on do have differing amounts of skills.
I've used Cadavre's example and am going to return 10 skill columns which should be more than enough. If it isnt, I can just add more. 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply