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.

Looks to me like you might be able to use [unpivot.](https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx) Here's an [Example](http://stackoverflow.com/questions/19055902/unpivot-with-column-name) — xQbert, Jun 12 '15 at 18:01
A bit off topic but why the nolocks? Do you know all the "benefits"...err pitfalls of that particular query hint? http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ — Sean Lange, Jun 12 '15 at 18:17
@Sean : I am using nolock in my real query. This is just sample query. — Ziggler, Jun 12 '15 at 18:19
Not sure I follow...you should read that article and understand the evils of that hint. — Sean Lange, Jun 12 '15 at 18:21

score 8 · Answer 1 · answered Jun 12 '15 at 17:59

8

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.

answered Jun 12 '15 at 17:59

Gordon Linoff

1,242,037
58
646
786

I updated my question and more info about my requirement. Thanks – Ziggler Jun 12 '15 at 18:12
Thanks I used UNION ALL – Ziggler Jun 12 '15 at 19:03
@Ziggler . . . Given that your table has changed into a query, I think you would get better performance with `cross apply`. – Gordon Linoff Jun 12 '15 at 21:29

score 2 · Accepted Answer · answered Jun 12 '15 at 18:36

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

I did what you said but in my second select query I used INNER JOIN instead of LEFT JOIN. I am marking this as answer. — Ziggler, Jun 12 '15 at 19:05

score 1 · Answer 3 · answered Jun 12 '15 at 18:00

1

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

answered Jun 12 '15 at 18:00

Kevin Whalen

31
1
6

I updated my question and more info about my requirement. Thanks – Ziggler Jun 12 '15 at 18:12
Thanks I used UNION ALL. – Ziggler Jun 12 '15 at 19:03

SQL : Split one row into two rows depending on column

3 Answers3