Transpose a particular row to columns

  • I am seeing lot of examples of PIVOT to do this, but my problem is different.
    I have a table like this

    I want to select one row among this and get the result as 

    Any dynamic way of getting this?

  • mahrajmca - Wednesday, June 20, 2018 4:06 PM

    I am seeing lot of examples of PIVOT to do this, but my problem is different.
    I have a table like this

    I want to select one row among this and get the result as 

    Any dynamic way of getting this?

    Here is a quick example of the query to transpose the set
    😎


    USE TEEST;
    GO
    SET NOCOUNT ON;

    ;WITH SAMPLE_DATA(CID,COL1,COL2,COL3,COL4,COL5) AS
    (
      SELECT 1,'A','B','C','D','E' UNION ALL
      SELECT 2,'F','G','H','I','J'
    )
    SELECT
      SD.CID
     ,X.[Name]
     ,X.[Value]
    FROM SAMPLE_DATA  SD
    CROSS APPLY
    (
      SELECT 'COL1', SD.COL1 UNION ALL
      SELECT 'COL2', SD.COL2 UNION ALL
      SELECT 'COL3', SD.COL3 UNION ALL
      SELECT 'COL4', SD.COL4 UNION ALL
      SELECT 'COL5', SD.COL5
    ) X([Name],[Value]);

    To do this dynamically, one has to retrieve the metadata and construct the query in dynamic SQL code.

  • Assuming you have an identity on the input table, to identify the row (row_id) you could try something like this:

    1. Identify the object_id:

    select *
    from sys.objects
    where object_id = object_id('input_table')

    2. Get all the column names and put them into a table:

    create table output_table ([name] varchar(50), [value] varchar(10))
    insert into output_table([name])
    select name from sys.columns where object_id = object_id(found at step1)
    and [name] <> 'row_id'

    3. update the output_table created at Step 2 with the Values:

    declare @sql nvarchar(max) = ''
    select @sql = @sql + '' + char(10) + char(13)

    select @sql = @sql
                + ' update output_table '
                + ' set value = ' + name
                + ' from output_table
                cross join tbl
                where [name] = ''' + name + ''''
                +'    and row_id = 1'+ char(10) + char(13)
    from output_table
    exec( @sql)

  • ralu_k_17 - Thursday, June 21, 2018 1:39 AM

    Assuming you have an identity on the input table, to identify the row (row_id) you could try something like this:

    Please don't. That's a pretty inefficient way of doing it.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Good catch Luis 😉
    😎
    Without further information, all input is speculative and not accurate enough.

  • Luis Cazares - Thursday, June 21, 2018 7:56 AM

    ralu_k_17 - Thursday, June 21, 2018 1:39 AM

    Assuming you have an identity on the input table, to identify the row (row_id) you could try something like this:

    Please don't. That's a pretty inefficient way of doing it.

    Hello Luis!

    I didn't really understand what you meant by this. Are you referring strictly to the Identity column of the input table? Or the whole solution?
    Also, if you have some time could you please explain a bit more as to why it is inefficient ? Or just direct me to some resource to read upon?

    Thank you so much!

  • ralu_k_17 - Friday, June 22, 2018 1:40 AM

    Luis Cazares - Thursday, June 21, 2018 7:56 AM

    ralu_k_17 - Thursday, June 21, 2018 1:39 AM

    Assuming you have an identity on the input table, to identify the row (row_id) you could try something like this:

    Please don't. That's a pretty inefficient way of doing it.

    Hello Luis!

    I didn't really understand what you meant by this. Are you referring strictly to the Identity column of the input table? Or the whole solution?
    Also, if you have some time could you please explain a bit more as to why it is inefficient ? Or just direct me to some resource to read upon?

    Thank you so much!

    I'm referring to the whole solution. Basically, you're reading the table and writing to a new one once for every column. After that, you still need to read from that new table. If you look at Eirikur's example, the code is just reading the table once.
    There's also the problem that it's only handling one row at a time (and that can be an issue). Once you need to define a specific ID, you need to change the way you call your dynamic SQL (for best practices).
    The concatenation method that you used is inefficient and using the FOR XML PATH option is a better solution on versions before 2017 where STRING_AGG was included. Here's an article on that, but there might be others: https://blogs.msdn.microsoft.com/arvindsh/2016/06/27/the-aggregate-concatenation-t-sql-anti-pattern/

    Here's a dynamic version of Eirikur's code.

    /*Creating sample data, this is not part of the solution*/
    CREATE TABLE dbo.SampleData(
      CID int NOT NULL,
      COL1 varchar(15) NULL,
      COL2 varchar(15) NULL,
      COL3 varchar(15) NULL,
      COL4 varchar(15) NULL,
      COL5 varchar(15) NULL
    );

    INSERT INTO dbo.SampleData(CID,COL1,COL2,COL3,COL4,COL5)
    SELECT 1,'A','B','C','D','E' UNION ALL
    SELECT 2,'F','G','H','I','J'
    GO
    /*Solution starts here*/
    DECLARE @sql NVARCHAR(MAX);

    SELECT @sql = N'SELECT ' + NCHAR(10) +
        N'    SD.CID  ' + NCHAR(10) +
        N'    ,X.[Name] ' + NCHAR(10) +
        N'    ,X.[Value] ' + NCHAR(10) +
        N'FROM dbo.SampleData SD ' + NCHAR(10) +
        N'CROSS APPLY ' + NCHAR(10) +
        N'(' +
        STUFF(( SELECT REPLACE( 'UNION ALL SELECT ''<<column_name>>'', SD.<<column_name>>' + CHAR(10), '<<column_name>>', name)
        FROM sys.columns
        WHERE object_id = object_id('dbo.SampleData', 'U')
        and [name] <> 'CID'
        FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)'), 1, 9, '') + N') X([Name],[Value])' + NCHAR(10) +
        N'WHERE SD.CID = @Id;'
      
    PRINT @sql;
    EXECUTE sp_executesql @sql, N'@Id int', @Id = 1;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thank you Luis, for taking the time and explaining it. I understood now, and agree that given a large set of data, my solution would have painful performance, in comparison with yours

Viewing 8 posts - 1 through 7 (of 7 total)

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