Transpose sql table rows into columns

  • I want to transpose sql table rows to columns. I have written a query for that already but its taking a very long time (around 15 min) as the number of rows in the table is high like 8M+.

    Is there any other way to write the query to get the same output with improved performance.

    (Cannot change the table structure and don't have permission for indexing or such admin stuff). So need to manipulate the query to an improved version.

    PFA the script to create the tables, load sample data and the select query that I have written currently which is taking long time to complete.

    Attachments:
    You must be logged in to view attached files.
  • It is difficult to design any queries without knowing anything about your tables and their indexes.

    That said, this might help.

    SELECT i.ItemID
    , i.ItemName
    , s.Dimension
    , s.Model
    , s.WarehouseLoc
    FROM dbo.#Item AS i
    LEFT JOIN (SELECT spc.ItemID
    , Dimension = MAX(CASE WHEN spc.Spec = 'Dimn' THEN spc.SpecValue END)
    , Model = MAX(CASE WHEN spc.Spec = 'Model' THEN spc.SpecValue END)
    , WarehouseLoc = MAX(CASE WHEN spc.Spec = 'WarehouseLoc' THEN spc.SpecValue END)
    FROM dbo.#ItemSpecs AS spc
    GROUP BY spc.ItemID
    ) AS s ON i.ItemID = s.ItemID
  • This is a similar query that might perform differently.  You need to test

    SELECT  i.ItemID
    , i.ItemName
    , Dimension = MAX(CASE WHEN spc.Spec = 'Dimn' THEN spc.SpecValue END)
    , Model = MAX(CASE WHEN spc.Spec = 'Model' THEN spc.SpecValue END)
    , WarehouseLoc = MAX(CASE WHEN spc.Spec = 'WarehouseLoc' THEN spc.SpecValue END)
    FROM dbo.#Item AS i
    INNER JOIN dbo.#ItemSpecs AS spc ON i.ItemID = spc.ItemID
    GROUP BY i.ItemID, i.ItemName
  • This looks like a simple pivot.

    SELECT ItemId, ItemName,  Dimn as Dimension, Model, WarehouseLoc
    FROM (
    select a.ItemID, a.ItemName, b.Spec, b.SpecValue
    from dbo.#Item a
    join dbo.#ItemSpecs b
    on b.ItemID = a.ItemID)
    as SourceTable
    PIVOT (
    MAX(SpecValue) FOR Spec IN
    ([Dimn], [Model], [WarehouseLoc])
    ) AS PivotTable;
  • This looks like a simple pivot.

    SELECT ItemId, ItemName,  Dimn as Dimension, Model, WarehouseLoc
    FROM (
    select a.ItemID, a.ItemName, b.Spec, b.SpecValue
    from dbo.#Item a
    join dbo.#ItemSpecs b
    on b.ItemID = a.ItemID)
    as SourceTable
    PIVOT (
    MAX(SpecValue) FOR Spec IN
    ([Dimn], [Model], [WarehouseLoc])
    ) AS PivotTable;
  • This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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