SQL : Split one row into two rows depending on column

Question

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.

Answer 1

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.