question

rpowell avatar image
rpowell asked

How to extract data from a table with multiple columns. But insert the records from each column into a row one behind the other

format.jpg

Here is the data I have in a table. I would like to read each column into one row. And insert the records from each column into a row one behind the other. See my example below.

What would be the way to accomplish this? Would a cursor loop be the way?

Table data sample

ImportCode PatronID BarCode %C121Q 676450 4FUSD001121118 %C121Q 663483 4FUSD001120839 %C121Q 661977 4FUSD000623255

Desired output format

%C121Q 676450

4FUSD001121118

%C121Q

663483

4FUSD001120839

%C121Q

661977

4FUSD000623255

formatting
format.jpg (16.8 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ankushparab avatar image
ankushparab answered

This can be achieved using UNPIVOT feature.. here is sample code based on your example.

CREATE TABLE test(ImportCode VARCHAR(100), PatronID INT, BarCode VARCHAR(100))

INSERT INTO dbo.test ( ImportCode, PatronID, BarCode )
VALUES ( '%C121Q', 676450, '4FUSD001121118' ),
('%C121Q', 663483,'4FUSD001120839'),
('%C121Q', 661977 , '4FUSD000623255')

SELECT List FROM (

SELECT ImportCode,
CAST(PatronID AS VARCHAR(100)) PatronID,

BarCode FROM test) p

UNPIVOT (List FOR Employee IN (ImportCode, PatronID, BarCode ) )AS unpvt;

GO

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.