Query to list rows into columns

  • Hi,

    I have requirement to list each row into column. I have tried with pivot query but unable to get it. Can you please help. I am using SQL Server 2008 database.

    Here is the sample data:

    CREATE TABLE dbo.test (

    action_id numeric,

    action VARCHAR(20) NOT NULL,

    action_by VARCHAR(20) NOT NULL,

    action_date VARCHAR(20) NOT NULL

    );

    INSERT INTO dbo.test (action_id,action, action_by,action_date)

    VALUES (1,'Action1', 'xxxx','12/31/2015'),

    (1,'Action2', 'xxxx','12/28/2015'),

    (1,'Action3', 'yyyy','12/29/2015')

    Output required as follows:

    ----------------------------

    action1

    xxxx

    12/31/2015

    ---------

    action2

    xxxxx

    12/28/2015

    ---------

    action3

    yyyy

    12/29/2015

    Thanks

    NLV

  • Quick suggestion

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    IF OBJECT_ID(N'dbo.test') IS NOT NULL DROP TABLE dbo.test;

    GO

    CREATE TABLE dbo.test (

    action_id numeric,

    action VARCHAR(20) NOT NULL,

    action_by VARCHAR(20) NOT NULL,

    action_date VARCHAR(20) NOT NULL

    );

    INSERT INTO dbo.test (action_id,action, action_by,action_date)

    VALUES (1,'Action1', 'xxxx','12/31/2015'),

    (1,'Action2', 'xxxx','12/28/2015'),

    (1,'Action3', 'yyyy','12/29/2015');

    SELECT

    X.OUT_COL

    FROM dbo.test T

    CROSS APPLY

    (

    SELECT CONVERT(VARCHAR(50),action_id,0) UNION ALL

    SELECT action UNION ALL

    SELECT action_by UNION ALL

    SELECT action_date

    ) AS X(OUT_COL)

    ;

    Results

    OUT_COL

    ------------

    1

    Action1

    xxxx

    12/31/2015

    1

    Action2

    xxxx

    12/28/2015

    1

    Action3

    yyyy

    12/29/2015

  • Thanks Erikson for your help.

  • NLV (8/10/2015)


    Thanks Erikson for your help.

    You are very welcome

    😎

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply