Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET SQL-Server
There are three rows
 
      Id  marks 
Row1: 1   15   
Row2: 1   16   
Row3: 1   17   
and I want to merge all the three rows into a single row, can anybody help me please!!!!
 
output should be like below
      Id  marks1 marks2 marks3 
Row1: 1       15   16      17
 
please help me..
thank you.
Posted 6-Jun-12 9:35am
Edited 6-Jun-12 9:42am
v2

1 solution

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

Solution 2

It sounds like you need to "pivot"
 
Using the PIVOT sql command you can rotate and restructure the data although I'm not sure you can get it working for n columns as a static query.
 
You need an extra id in the marks table so each mark against each id has own sequence too. So the primary key would be id, markid.
 
      Id  MarkId  Mark
Row1  1   1       15
Row2  1   2       16
Row3  1   3       17
Row4  2   1       25
Row5  2   2       26
Row6  2   3       27
You can then use PIVOT to rotate the selection.
SELECT Id, [1], [2], [3]
FROM
    (SELECT Id, MarkId, Mark FROM MarksTable) AS SourceTable
    PIVOT
    (Min(Mark) FOR MarkId IN ([1], [2], [3])) AS PivotTable
 
This will output like this:
Id  "1"  "2"  "3"
1   15   16   17
2   25   26   27
 
The following is a full copy and paste script which follows your example and works:
 
SELECT Id = 1, MarkId = 1, Mark = 15 INTO #Temp UNION
SELECT Id = 1, MarkId = 2, Mark = 16 UNION
SELECT Id = 1, MarkId = 3, Mark = 17 UNION
SELECT Id = 2, MarkId = 1, Mark = 25 UNION
SELECT Id = 2, MarkId = 2, Mark = 26 UNION
SELECT Id = 2, MarkId = 3, Mark = 27
 
SELECT * FROM #Temp
 
SELECT Id, [1], [2], [3]
FROM
    (SELECT Id, MarkId, Mark FROM #Temp) AS SourceTable
    PIVOT
    (Min(Mark) FOR MarkId IN ([1], [2], [3])) AS PivotTable
 
DROP TABLE #Temp
 
Using a dynamic query you can build up a select for n number of marks by generating the SQL on the fly and running it using SP_ExecuteSQL.
 
The following article shows how to build a PIVOT query using dynamic SQL
 
http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx[^]
  Permalink  
v4
Comments
idle63 at 6-Jun-12 16:17pm
   
That sounds fine, much more better then my dirty approach :) my 5
allagi.arun at 6-Jun-12 16:20pm
   
thank you but i m geeing error.. Incorrect syntax near for..
Stephen Hewison 2 at 6-Jun-12 16:54pm
   
Sorry you're correct. Two changes, the Id in the first row of the select should be without quotes and the first field within PIVOT must be an aggregation function. In this instance as we're selecting against the primary key there's only on row to aggregate. I'll update my answer in just a moment.
allagi.arun at 9-Jun-12 15:37pm
   
okay i'll be waiting for it..
Stephen Hewison 2 at 9-Jun-12 17:29pm
   
It's done already.
VJ Reddy at 6-Jun-12 19:49pm
   
Good answer. 5!
Sandeep Mewara at 7-Jun-12 4:36am
   
5!

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 525
1 CPallini 505
2 George Jonsson 353
3 OriginalGriff 220
4 Pheonyx 218
0 OriginalGriff 4,813
1 CPallini 3,890
2 Sergey Alexandrovich Kryukov 3,274
3 George Jonsson 2,552
4 Gihan Liyanage 2,186


Advertise | Privacy | Mobile
Web04 | 2.8.140905.1 | Last Updated 6 Jun 2012
Copyright © CodeProject, 1999-2014
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