Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
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 6-Mar-13 0:07am
v2
Comments
Maciej Los at 6-Mar-13 7:57am
   
Why do you want to "merge" it into one row?
Rate this: bad
good
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
  Permalink  
v2
Comments
deepakaitr12345 at 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: bad
good
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
  Permalink  
Comments
RedDK at 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)

  Print Answers RSS
0 OriginalGriff 6,803
1 Sergey Alexandrovich Kryukov 6,377
2 DamithSL 5,421
3 Manas Bhardwaj 4,841
4 Maciej Los 4,330


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