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
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
