Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
select * FROM Tb_SCh_TIme_Table P PIVOT (MAX(Faculty_Code) FOR Session IN ([1],[2],[3] ,[4])) AS PVT  where course = 'PH2' order by course;
from my above query output as follows;
date        course    1   2   3   4
 15/1/2013     AFF     CM
 15/1/2013     AFF     NR

i want the below output as follows;
  date        course    1     2   3   4
 15/1/2013     AFF     CM,NR
how can i do.

using PIVOT query we can concatenation the rows and put in a single row.

how can id using my above query.
Posted 22-Jan-13 19:59pm
Edited 23-Jan-13 0:52am
v2
Comments
Tharaka MTR at 23-Jan-13 3:43am
   
this seems re-post of following question
http://www.codeproject.com/Questions/532803/howplustoplususeplustowoplusqueryplusmakeplusinplu

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Here is the complete query according to me

SELECT *
FROM (
SELECT [Date],[Course],[Session],
    SUBSTRING((SELECT ','+ [Faculty_Code]
        FROM [Tb_SCh_TIme_Table ] dp2 
        WHERE dp2.[Date] = dp1.[Date] and dp2.[Course]=dp1.[Course] and dp2.[Session]=dp1.[Session]
    FOR XML PATH('')), 2, 8000) AS [Faculty_Code]
FROM [Tb_SCh_TIme_Table ] dp1
GROUP BY [Date],[Session],[Course]
) P
PIVOT (
MAX([Faculty_Code])
FOR [Session] IN ([1],[2],[3] ,[4])
) AS PVT
WHERE [Course]='PH2'
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Suvendu Shekhar Giri 265
1 Sergey Alexandrovich Kryukov 235
2 Andy Lanng 185
3 PIEBALDconsult 180
4 Black_Rose 130
0 Sergey Alexandrovich Kryukov 6,360
1 OriginalGriff 5,878
2 Peter Leow 2,514
3 Maciej Los 2,263
4 Abhinav S 2,249


Advertise | Privacy | Mobile
Web01 | 2.8.150414.1 | Last Updated 23 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100