Combine Column into 1 row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Combine Column into 1 row

Author

Topic

byka
Starting Member

18 Posts

Posted - 2014-06-09 : 13:54:33

how can I combine the following result into 1 row?
Deductible DeductibleType Notes
NULL NULL Ded applies
NULL Per Policy Year NULL
$500 NULL NULL

My end results should be
Deductible DeductibleType Notes
$500 Per Policy Year Ded applies

byka

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-09 : 15:56:54

The trick is how to relate the rows. Assume that there are 3 million rows, not just three. In that case, how would you match up the rows to get the correct result? They do not appear to share any sort of key or id column

byka
Starting Member

18 Posts

Posted - 2014-06-10 : 07:29:58

I do have Keys:FeatureID.
The end results should be only 1 row. I have additional logic (no posted here) to get only 3 rows and now I am stuck to get into 1

byka

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-10 : 08:02:50

I am not sure ...


SELECT (  SELECT  t.Deductible 
		  FROM #text t
		  WHERE t.Deductible IS NOT NULL
		) AS Deductible,
	   (  SELECT  t.DeductibleType 
		  FROM #text t
		  WHERE t.DeductibleType IS NOT NULL
		) AS DeductibleType,
	   (  SELECT  t.varNotes 
		  FROM #text t
		  WHERE t.varNotes IS NOT NULL
		) AS varNotes

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-10 : 08:26:02

Tried my best to modify


CREATE TABLE #text(Deductible varchar(MAX),DeductibleType varchar(MAX),varNotes varchar(MAX))
INSERT INTO #text
SELECT NULL,NULL, 'Ded applies' UNION ALL
SELECT NULL,'Per Policy Year',NULL UNION ALL
SELECT '$500',NULL,NULL UNION ALL
SELECT NULL,NULL, 'Ded ' UNION ALL
SELECT NULL,'Year',NULL UNION ALL
SELECT '$5400',NULL,NULL 

SELECT	    	        a.Deductible
		      , b.DeductibleType
		      , c.varNotes
FROM          ( SELECT  t.Deductible 
		       ,RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
		        FROM #text t
		        WHERE t.Deductible IS NOT NULL     )  a
INNER JOIN    ( SELECT  t.DeductibleType 
		       ,RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
		        FROM #text t
		        WHERE t.DeductibleType IS NOT NULL ) b
ON		        a.RowNum = b.RowNum
INNER JOIN    ( SELECT  t.varNotes 
		       ,RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
		        FROM #text t
		        WHERE t.varNotes IS NOT NULL       ) c
ON	                b.RowNum = c.RowNum

DROP TABLE #text

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

byka
Starting Member

18 Posts

Posted - 2014-06-10 : 08:43:51

It works perfectly.. the only question I have is... how do I get Code column in my select. I am getting 2 rows now and I need to include Code....
Code Deductible DeductibleType Notes
PREF NULL NULL Ded
NPRF NULL NULL Deductible
NPRF $1_000 NULL NULL
PREF $500 NULL NULL
NPRF NULL Per Policy Year NULL
PREF NULL Per Policy Year NULL

byka

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-11 : 00:22:28

May be..


CREATE TABLE #text(code varchar(MAX),Deductible varchar(MAX),DeductibleType varchar(MAX),varNotes varchar(MAX))
INSERT INTO #text
SELECT 'PREF',NULL,NULL, 'Ded applies' UNION ALL
SELECT 'NPRF',NULL,'Per Policy Year',NULL UNION ALL
SELECT 'NPRF','$500',NULL,NULL UNION ALL
SELECT 'PREF',NULL,NULL, 'Ded ' UNION ALL
SELECT 'NPRF',NULL,'Year',NULL UNION ALL
SELECT 'PREF','$5400',NULL,NULL 

SELECT			a.code	       
		      , a.Deductible
		      , b.DeductibleType
		      , c.varNotes
FROM          ( SELECT  t.code 
		       ,t.Deductible 
		       ,RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
		        FROM #text t
		        WHERE t.Deductible IS NOT NULL     )  a
INNER JOIN    ( SELECT  t.DeductibleType 
		       ,RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
		        FROM #text t
		        WHERE t.DeductibleType IS NOT NULL ) b
ON		        a.RowNum = b.RowNum
INNER JOIN    ( SELECT  t.varNotes 
		       ,RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
		        FROM #text t
		        WHERE t.varNotes IS NOT NULL       ) c
ON	                b.RowNum = c.RowNum

DROP TABLE #text

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources