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

Question

question

rpowell asked Aug 17, '20

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

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

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

Answer 1 · 2020-08-17T14:25:04Z

ankushparab answered Aug 17, '20

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

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

question