12,453,873 members (79,823 online)
Rate this:
See more:
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```

thank you.
Posted 6-Jun-12 9:35am
Updated 6-Jun-12 9:42am
Nikfazan14.7K
v2

Rate this:

## 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[^]
v4
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

VJ Reddy 6-Jun-12 19:49pm

Sandeep Mewara 7-Jun-12 4:36am

5!

Top Experts
Last 24hrsThis month
 Maciej Los 330 OriginalGriff 210 Dave Kreskowiak 115 ppolymorphe 83 Bernhard Hiller 80
 OriginalGriff 6,373 ppolymorphe 3,858 Karthik Bangalore 3,625 Maciej Los 3,430 Richard Deeming 2,835