March 4, 2011 at 4:14 am
create table #tab1( empno int, mark1 int, rank1 int)
insert into #tab1 values (1,50,2)
insert into #tab1 values (1,60,3)
insert into #tab1 values (1,70,4)
insert into #tab1 values (2,10,1)
insert into #tab1 values (2,20,2)
desired output is
empno mark1 rank1
1 | 50,60,70 | 2,3
2 | 10,20 | 1,2
can tht be acheived?.. any suggestion pls?
Thanks in Advance
March 4, 2011 at 4:18 am
March 4, 2011 at 4:53 am
Once you've read that article, this code should make sense:
SELECT CONVERT(VARCHAR(12), Emp.empno) + ' | ' +
STUFF(CA1.marks.value('./text()[1]', 'varchar(max)'), 1, 1, '') + ' | ' +
STUFF(CA2.ranks.value('./text()[1]', 'varchar(max)'), 1, 1, '')
FROM (
SELECT DISTINCT T1.empno
FROM #tab1 AS T1
) AS Emp
CROSS
APPLY (
SELECT ',' + CONVERT(VARCHAR(12), T2.mark1)
FROM #tab1 AS T2
WHERE T2.empno = Emp.empno
ORDER BY
T2.empno
FOR XML PATH(''), TYPE
) AS CA1 (marks)
CROSS
APPLY (
SELECT ',' + CONVERT(VARCHAR(12), T3.rank1)
FROM #tab1 AS T3
WHERE T3.empno = Emp.empno
ORDER BY
T3.empno
FOR XML PATH(''), TYPE
) AS CA2 (ranks)
;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 4, 2011 at 5:00 am
Really Nice Solution given at the link http://www.sqlservercentral.com/articles/comma+separated+list/71700/
WITH CTE AS
(
SELECT DISTINCT
empno
FROM #tab1
)
SELECT empno,
mark1 = STUFF((SELECT ',' + convert(char(50),mark1)
FROM #tab1
WHERE empno = CTE.empno
ORDER BY mark1
FOR XML PATH(''),
TYPE).value('.','varchar(max)'),1,1,''),
rank1 = STUFF((SELECT ',' + convert(char(50),rank1)
FROM #tab1
WHERE empno = CTE.empno
ORDER BY rank1
FOR XML PATH(''),
TYPE).value('.','varchar(max)'),1,1,'')
FROM CTE
ORDER BY empno;
March 4, 2011 at 5:05 am
ramarkishna (3/4/2011)
Really Nice Solution
That's almost there, but the output isn't quite right (multiple columns and huge blank spaces). Also the performance will not be as good as the solution already posted.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 4, 2011 at 5:17 am
SQLkiwi (3/4/2011)
Once you've read that article, this code should make sense:
SELECT CONVERT(VARCHAR(12), Emp.empno) + ' | ' +
STUFF(CA1.marks.value('./text()[1]', 'varchar(max)'), 1, 1, '') + ' | ' +
STUFF(CA2.ranks.value('./text()[1]', 'varchar(max)'), 1, 1, '')
FROM (
SELECT DISTINCT T1.empno
FROM #tab1 AS T1
) AS Emp
CROSS
APPLY (
SELECT ',' + CONVERT(VARCHAR(12), T2.mark1)
FROM #tab1 AS T2
WHERE T2.empno = Emp.empno
ORDER BY
T2.empno
FOR XML PATH(''), TYPE
) AS CA1 (marks)
CROSS
APPLY (
SELECT ',' + CONVERT(VARCHAR(12), T3.rank1)
FROM #tab1 AS T3
WHERE T3.empno = Emp.empno
ORDER BY
T3.empno
FOR XML PATH(''), TYPE
) AS CA2 (ranks)
;
Thanks a lot.... but here #tab1 will be already derived from CTE.... so it will take more table scan... u suggest me to go with this?
March 4, 2011 at 5:28 am
MonsterRocks (3/4/2011)
Thanks a lot.... but here #tab1 will be already derived from CTE.... so it will take more table scan... u suggest me to go with this?
It depends on how expensive it is to run the CTE more than once. If the output from the CTE is relatively small, you could consider storing it in a temporary table beforehand. My post was to aid you in understanding how to apply the technique mentioned in the article - not necessarily as a cut-and-paste solution to your real problem.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 4, 2011 at 8:49 am
i am not fond of copy and paste... just want to a better way to achieve it... since i m a beginner i dont knw how efficient to use more than one CROSS APPLY along with a CTE..thats y i came to you experienced people....Guidance and idea i expect.... thanks a lot for ur effort and valuable ideas
March 4, 2011 at 8:36 pm
Dave Ballantyne (3/4/2011)
Try this link http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Thanks for the plug Dave.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 4, 2011 at 8:37 pm
SQLkiwi (3/4/2011)
Once you've read that article, this code should make sense:
SELECT CONVERT(VARCHAR(12), Emp.empno) + ' | ' +
STUFF(CA1.marks.value('./text()[1]', 'varchar(max)'), 1, 1, '') + ' | ' +
STUFF(CA2.ranks.value('./text()[1]', 'varchar(max)'), 1, 1, '')
FROM (
SELECT DISTINCT T1.empno
FROM #tab1 AS T1
) AS Emp
CROSS
APPLY (
SELECT ',' + CONVERT(VARCHAR(12), T2.mark1)
FROM #tab1 AS T2
WHERE T2.empno = Emp.empno
ORDER BY
T2.empno
FOR XML PATH(''), TYPE
) AS CA1 (marks)
CROSS
APPLY (
SELECT ',' + CONVERT(VARCHAR(12), T3.rank1)
FROM #tab1 AS T3
WHERE T3.empno = Emp.empno
ORDER BY
T3.empno
FOR XML PATH(''), TYPE
) AS CA2 (ranks)
;
Hmm, I should have thought of using CROSS APPLY in that article. Thanks for enlightening us (yet again)!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply