January 22, 2025 at 9:13 am
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.
January 22, 2025 at 11:44 am
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
January 22, 2025 at 11:52 am
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
January 22, 2025 at 4:13 pm
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;
January 22, 2025 at 4:13 pm
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;
January 23, 2025 at 10:04 am
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