October 25, 2012 at 8:29 am
Hi Guys,
Id like to covert output from columns to rows.
Currently Selecting * from a table returns.
ACCOUNTNOSkill
C1.NET
C1HTML
C1CSS
C2.NET
C2CSS
C2XML
C3CSS
C3.NET
C3XML
Im looking to return:
ACCOUNTNOSkill1,Skill2, Skill3
C1.NETCSSHTML
C2.NETCSSXML
C3.NETCSSXML
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
(
ACCOUNTNOVARCHAR(5),
SkillVARCHAR(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
(
ACCOUNTNOVARCHAR(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
(
ACCOUNTNOVARCHAR(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