You must Sign In to post a response.
  • Category: SQL Server

    Convert Rows to Columns

    Hi I have table as shown Below

    Id Workstream DocumentName DocumentType
    1 TWE Document2 SD
    1 TWE Document3 SD
    1 TWE Document4 CI
    1 TWE Document5 Oth
    1 TWE Document6 CI
    1 TWE Document7 TD
    1 TWE Document8 Oth
    I need to get in below format

    Workstream SD CI Oth TD
    TWE Document2 Document4 Document5 Document7
    TWE Dcoument3 Document8
  • #764455
    Hi,

    Use PIVOT Table to achieve above output, Go through the below link to know more about PIVOT Table https://www.google.co.in/search?q=pivot+table+in+sql+server&oq=pivot+table+in+sql+server&aqs=chrome..69i57.5690j0j7&sourceid=chrome&es_sm=93&ie=UTF-8

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #764456
    We will not able to achieve this with PIVOT Table.

  • #764457
    Hi,
    -- Try this script
    CREATE TABLE #TempTable1
    ([Id] int, [Workstream] varchar(20), [DocumentName] varchar(20), [DocumentType] varchar(20))
    INSERT INTO #TempTable1
    ([Id], [Workstream], [DocumentName], [DocumentType])
    VALUES
    (1, 'TWE', 'Document2', 'SD'),
    (1, 'TWE', 'Document3', 'SD'),
    (1, 'TWE', 'Document4', 'CI'),
    (1, 'TWE', 'Document5', 'Oth'),
    (1, 'TWE', 'Document6', 'CI'),
    (1, 'TWE', 'Document7', 'TD'),
    (1, 'TWE', 'Document8', 'Oth')
    select (Workstream),
    (case when DocumentType = 'SD' then [DocumentName] end) SD,
    (case when DocumentType = 'CI' then [DocumentName] end) CI,
    (case when DocumentType = 'Oth' then [DocumentName] end) Oth,
    (case when DocumentType = 'TD' then [DocumentName] end) TD
    from #TempTable1
    -- at final just ignore null values from result ...
    Hope it helps.
    Regards,
    Shashikant Gurav
    shashikantgurav22@gmail.com

  • #764466
    You can try the PIVOT query.
    Following is tips to do this

    SELECT *
    FROM (
    SELECT
    Workstream , DocumentName, DocumentType
    FROM TableName
    ) as s
    PIVOT
    (
    // USE you logic
    )AS pv

    By Nathan
    Direction is important than speed

  • #764518
    The basic syntax for a PIVOT relational operator to Converting Rows to Columns (PIVOT) and Columns to Rows (UNPIVOT) in SQL looks like this code snippet:
    SELECT <<YourColumnNames>> 
    FROM <<ExampleTableName>>
    PIVOT
    (
    AggregateFunction(<<ColumnToBeAggregated>>)
    FOR PivotColumn IN (<<PivotColumnValues>>)
    ) AS <<Alias>>


Sign In to post your comments