13,047,502 members (64,523 online)
Rate this:
Please Sign up or sign in to vote.
See more:
HI Friends,

I have a problem in the following table,

```1	555	pass	yes	NULL	NULL
2	555	pass	no	NULL	NULL
3	555	NULL	no	NULL	NULL
4	555	NULL	no	NULL	NULL
5	555	fail	no	NULL	NULL
6	555	NULL	no	NULL	NULL
7	555	pass	no	NULL	NULL
8	555	NULL	no	NULL	NULL```

I want to convert all the vertical records in to the horizontal i.e.
1 ,555 ,pass ,yes ,NULL ,NULL ,2 ,555 ,Pass ,no ,NULL ,NULL ,3 ,555 ,NULL ,no ,NULL ,NULL...

but here i am not using the aggrigate function so that i can't use pivot.

please suggest me how to do this to solve this in sql server.

Thanks
Posted 5-Mar-13 23:07pm
Updated 6-Mar-13 0:25am
v2
Comments
Maciej Los 6-Mar-13 7:57am

Why do you want to "merge" it into one row?

## 2 solutions

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

## Solution 1

Let me create a sample table with your values.
```SELECT
*
INTO MyTable
FROM
(
SELECT
1 AS SL,	555 AS ID,	'pass' AS [Status],	'yes' AS Qualified, NULL AS ST1,	NULL AS ST2
UNION ALL
SELECT
2	,555,	'pass', 	'no'	, NULL, 	NULL
UNION ALL
SELECT
3	,555,	NULL,	'no',	NULL	, NULL
UNION ALL
SELECT
4	,555,	NULL, 	'no'	, NULL, 	NULL
UNION ALL
SELECT
5	,555,	'fail',	'no'	, NULL, 	NULL
UNION ALL
SELECT
6	,555,	NULL, 	'no'	, NULL, 	NULL
UNION ALL
SELECT
7	,555,	'pass', 	'no'	, NULL, NULL
UNION ALL
SELECT
8	,555,	NULL, 	'no'	, NULL, NULL
)X```

And the query you need is here:
```SELECT SUBSTRING(MyRow,0,LEN(MyRow)) AS MyRow FROM
(
SELECT DISTINCT ID,
(
SELECT CONVERT(VARCHAR(10),SL)+ ','
+ CONVERT(VARCHAR(10),ID)+ ','
+ CONVERT(VARCHAR(10),COALESCE([Status],''))+ ','
+ CONVERT(VARCHAR(10),COALESCE(Qualified,''))+ ','
+ CONVERT(VARCHAR(10),COALESCE(ST1,''))+ ','
+ CONVERT(VARCHAR(10),COALESCE(ST2,''))+ ','
AS [text()]
FROM dbo.MyTable AS TBL_A WHERE TBL_A.ID=TBL_B.ID
FOR XML PATH('')
) AS MyRow
FROM dbo.MyTable AS  TBL_B
)
AS TBL_C```

The result

```MyRow
1,555,pass,yes,0,0,2,555,pass,no,0,0,3,555,,no,0,0,4,555,,no,0,0,5,555,fail,no,0,0,6,555,,no,0,0,7,555,pass,no,0,0,8,555,,no,0,0```
v2
Comments
deepakaitr12345 6-Mar-13 7:48am

Hi Kuthuparakkal Thanks it is very useful.

want to know one more thing is it possible to convert these values to column values

like column1 column2 column3 column4 column5

1 555 pass yes 0..........

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

## Solution 2

```SELECT SUBSTRING(C,1,LEN(C)-1)'MyRow' FROM (SELECT 1'A',(SELECT CONVERT(VARCHAR(10),SL)+ ','
+ CONVERT(VARCHAR(10),ID)+ ','
+ CONVERT(VARCHAR(10),COALESCE([Status],''))+ ','
+ CONVERT(VARCHAR(10),COALESCE(Qualified,''))+ ','
+ CONVERT(VARCHAR(10),COALESCE(ST1,''))+ ','
+ CONVERT(VARCHAR(10),COALESCE(ST2,''))+ ','
AS [text()]
FROM dbo.MyTable AS TBL_A
FOR XML PATH(''))AS C)T```
Comments
RedDK 6-Mar-13 12:06pm

This is not a bad answer. I can see why one would want to "single-line" a table ...

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

Top Experts
Last 24hrsThis month
 Graeme_Grant 152 Kornfeld Eliyahu Peter 110 Richard MacCutchan 104 Satya Prakash Swain 79 Atlapure Ambrish 70
 OriginalGriff 4,868 RickZeeland 1,864 ppolymorphe 1,748 F-ES Sitecore 1,553 Dave Kreskowiak 1,379

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 6 Mar 2013
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