CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ziggler
Ziggler

Reputation: 3500

SQL : Split one row into two rows depending on column

I am sorry if this is duplicate. Please point me to correct question. I am using SQL SERVER 2008. I am using below query since I need to get data from 3 tables.

SELECT qc.FileID as [FileID],    
qc.QID1 as [QID1],    
xqs.SID1 as [SID1],   
xqc.CID1 as [CID1],  
xqs.Comments as [SComments],  
xqc.Comments as [CComments]  
FROM QCTable(nolock) qc  
JOIN QCSectionTable (nolock) xqs ON qc.QCID = xqs.QCID  
LEFT JOIN QCChargeTable (nolock) xqc ON xqc.QCXrefID = xqs.QCXrefID

For above I am getting this like FieID1 SID1 SID1 CID1 SComments CComments

I have a row like below

FileID1  QID1 SID1 CID1 SComments  CComments

I need to split above row as

FileID1 QID1 SID1 null SComments
FileID1 QID1 SID1 CID1 CComments

Thanks in advance.

Upvotes: 4

Views: 38354

Answers (3)

FutbolFan
FutbolFan

Reputation: 13723

You could do something like this using UNION ALL:

SELECT 
     qc.FileID AS [FileID1]
    ,qc.QID1 AS [QID1]
    ,xqs.SID1 AS [SID1]
    ,NULL AS [CID1]    --assigning default value as null
    ,xqs.Comments AS [SComments]
FROM QCTable(NOLOCK) qc
JOIN QCSectionTable(NOLOCK) xqs ON qc.QCID = xqs.QCID
LEFT JOIN QCChargeTable(NOLOCK) xqc ON xqc.QCXrefID = xqs.QCXrefID

UNION ALL

SELECT 
     qc.FileID AS [FileID1]
    ,qc.QID1 AS [QID1]
    ,xqs.SID1 AS [SID1]
    ,xqc.CID1 AS [CID1]
    ,xqc.Comments AS [CComments]
FROM QCTable(NOLOCK) qc
JOIN QCSectionTable(NOLOCK) xqs ON qc.QCID = xqs.QCID
LEFT JOIN QCChargeTable(NOLOCK) xqc ON xqc.QCXrefID = xqs.QCXrefID

Upvotes: 2

Kevin Whalen
Kevin Whalen

Reputation: 31

SELECT FileID1, QID1, DID1, null, SComments
FROM table
UNION ALL
SELECT FileID1, QID1, SID1, CID1, CComments
FROM table

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The easiest way is union all:

select FileID1, QID1, SID1, null as cId1, SComments
from table t
union all
select  FileID1, QID1, SID1, cId1, CComments
from table t;

If you have a large amount of data, it can be a bit faster to do this using cross apply or a cross join:

select v.*
from table t cross apply
     (values (FileID1, QID1, SID1, null, SComments),
             (FileID1, QID1, SID1, cId1, CComments)
     ) v(FileID1, QID1, SID1, cId1, CComments);

The advantage is that this would scan the table only once.

Upvotes: 9

Related Questions