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
Go to Top of Page

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
Go to Top of Page

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.......
Go to Top of Page

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.......
Go to Top of Page

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
Go to Top of Page

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.......
Go to Top of Page
   

- Advertisement -