September 25, 2013 at 4:33 am
Dear All
Currently I am getting following result sets
Registerno SubjectCode SubjectName Qno Marks
12402223171906Quality And Reliability Engineering 14
12402223171906Quality And Reliability Engineering210
12402223171906Quality And Reliability Engineering311
12402223171906Quality And Reliability Engineering45
12402223171906Quality And Reliability Engineering59
14124223170903Power System Protection 1 3
14124223170903Power System Protection 2 4
14124223170903Power System Protection 3 6
14124223170903Power System Protection 43
14124223170903Power System Protection 51
But I want following result sets
Registerno SubjectCode SubjectName Qno1 Qno2 Qno3 Qno4 Qno5
12402223 171906 Quality And Reliability Engineering 4 10 11 5 9
14124223 170903 Power System Protection 3 4 6 3 1
Please help me
September 25, 2013 at 4:47 am
Concatenating Row Values in Transact-SQL[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 25, 2013 at 1:13 pm
This is the way that I would do it.
--Your Data
WITH your_table AS
(SELECT * FROM
(VALUES
(12402223,171906,'Quality And Reliability Engineering',1,4),
(12402223,171906,'Quality And Reliability Engineering',2,10),
(12402223,171906,'Quality And Reliability Engineering',3,11),
(12402223,171906,'Quality And Reliability Engineering',4,5),
(12402223,171906,'Quality And Reliability Engineering',5,9),
(14124223,170903,'Power System Protection',1,3),
(14124223,170903,'Power System Protection',2,4),
(14124223,170903,'Power System Protection',3,6),
(14124223,170903,'Power System Protection',4,3),
(14124223,170903,'Power System Protection',5,1))
AS youtable(Registerno, SubjectCode, SubjectName, Qno, Marks)
)
--Solution
SELECT p1.Registerno,
p1.SubjectCode,
marky.marks
FROM your_table p1
CROSS APPLY
(SELECT CAST(Marks AS varchar(10))+' '
FROM your_table p2
WHERE p1.SubjectCode=p2.SubjectCode AND p1.SubjectName=p2.SubjectName
FOR XML PATH('')) AS marky(marks)
GROUP BY p1.Registerno, p1.SubjectCode, marky.marks;
-- Itzik Ben-Gan 2001
September 25, 2013 at 1:37 pm
Many thanks to Alan for the sample data. I think I would do this a little differently because the user wants a cross tab here instead of all jammed into a single column.
Assuming you always have 5 values this will work.
WITH your_table AS
(SELECT * FROM
(VALUES
(12402223,171906,'Quality And Reliability Engineering',1,4),
(12402223,171906,'Quality And Reliability Engineering',2,10),
(12402223,171906,'Quality And Reliability Engineering',3,11),
(12402223,171906,'Quality And Reliability Engineering',4,5),
(12402223,171906,'Quality And Reliability Engineering',5,9),
(14124223,170903,'Power System Protection',1,3),
(14124223,170903,'Power System Protection',2,4),
(14124223,170903,'Power System Protection',3,6),
(14124223,170903,'Power System Protection',4,3),
(14124223,170903,'Power System Protection',5,1))
AS youtable(Registerno, SubjectCode, SubjectName, Qno, Marks)
)
select Registerno,
SubjectCode,
SubjectName,
MAX(case when Qno = 1 then Marks end) as Qno1,
MAX(case when Qno = 2 then Marks end) as Qno2,
MAX(case when Qno = 3 then Marks end) as Qno3,
MAX(case when Qno = 4 then Marks end) as Qno4,
MAX(case when Qno = 5 then Marks end) as Qno5
from your_table
group by Registerno, SubjectCode, SubjectName
If you have a variable amount you should look at the link(s) in my signature about cross tabs.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2013 at 8:27 am
Alan.B (9/25/2013)
...
marky.marks;
Made my day. 😀
September 26, 2013 at 12:33 pm
erikd (9/26/2013)
Alan.B (9/25/2013)
...
marky.marks;
Made my day. 😀
Didn't even notice it at first 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 26, 2013 at 10:09 pm
you can use PIVOT for the same
WITH your_table AS
(SELECT * FROM
(VALUES
(12402223,171906,'Quality And Reliability Engineering',1,4),
(12402223,171906,'Quality And Reliability Engineering',2,10),
(12402223,171906,'Quality And Reliability Engineering',3,11),
(12402223,171906,'Quality And Reliability Engineering',4,5),
(12402223,171906,'Quality And Reliability Engineering',5,9),
(14124223,170903,'Power System Protection',1,3),
(14124223,170903,'Power System Protection',2,4),
(14124223,170903,'Power System Protection',3,6),
(14124223,170903,'Power System Protection',4,3),
(14124223,170903,'Power System Protection',5,1))
AS youtable(Registerno, SubjectCode, SubjectName, Qno, Marks)
)
SELECT *
FROM (
SELECT Registerno, SubjectCode, SubjectName,
Qno = 'Qno'+Convert(Varchar,Qno),
Marks
FROM your_table
) V
pivot
(
MAX(Marks)
for Qno in (Qno1,Qno2,Qno3,Qno4,Qno5)
) piv
September 27, 2013 at 7:53 am
Harshad Sanghani (9/26/2013)
you can use PIVOT for the same
The pivot approach certainly works but you might want to read this article. http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]
It demonstrates that PIVOT can have some performance issues compared to the cross tab. Not by any means saying that one is preferred over the other, just pointing out that cross tab tends to be a little bit quicker.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2013 at 9:26 am
Koen Verbeeck (9/26/2013)
erikd (9/26/2013)
Alan.B (9/25/2013)
...
marky.marks;
Made my day. 😀
Didn't even notice it at first 😀
I'm just happy someone noticed 😀
-- Itzik Ben-Gan 2001
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply