SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Combine Column into 1 row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

byka
Starting Member

18 Posts

Posted - 06/09/2014 :  13:54:33  Show Profile  Reply with Quote
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
Aged Yak Warrior

797 Posts

Posted - 06/09/2014 :  15:56:54  Show Profile  Reply with Quote
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
Go to Top of Page

byka
Starting Member

18 Posts

Posted - 06/10/2014 :  07:29:58  Show Profile  Reply with Quote
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
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
106 Posts

Posted - 06/10/2014 :  08:02:50  Show Profile  Reply with Quote
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.......
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
106 Posts

Posted - 06/10/2014 :  08:26:02  Show Profile  Reply with Quote
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.......
Go to Top of Page

byka
Starting Member

18 Posts

Posted - 06/10/2014 :  08:43:51  Show Profile  Reply with Quote
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
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
106 Posts

Posted - 06/11/2014 :  00:22:28  Show Profile  Reply with Quote
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.......

Edited by - MuralikrishnaVeera on 06/11/2014 00:24:42
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000