Click here to Skip to main content
13,088,101 members (62,551 online)
Rate this:
Please Sign up or sign in to vote.
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
Updated 6-Jun-12 9:42am

1 solution

Rate this: bad
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]
    (SELECT Id, MarkId, Mark FROM MarksTable) AS SourceTable
    (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 Id, [1], [2], [3]
    (SELECT Id, MarkId, Mark FROM #Temp) AS SourceTable
    (Min(Mark) FOR MarkId IN ([1], [2], [3])) AS PivotTable

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[^]
idle63 6-Jun-12 16:17pm
That sounds fine, much more better then my dirty approach :) my 5
allagi.arun 6-Jun-12 16:20pm
thank you but i m geeing error.. Incorrect syntax near for..
Stephen Hewison 2 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 9-Jun-12 15:37pm
okay i'll be waiting for it..
Stephen Hewison 2 9-Jun-12 17:29pm
It's done already.
VJ Reddy 6-Jun-12 19:49pm
Good answer. 5!
Sandeep Mewara 7-Jun-12 4:36am

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web03 | 2.8.170813.1 | Last Updated 6 Jun 2012
Copyright © CodeProject, 1999-2017
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