SQL TRICKY QUERY QUIZ : SOLVE THIS QUERY IF YOU ARE EXPERT
SQL QUERY :
I have to select data from 2 tables which looks like this:
Table1:
ID Prob Cost Visible
-------------------------
12345 100 50 1
12346 90 333 1Table2:
ID Item1 Count
-----------------------
12345 555 3
12345 666 5
12345 777 20
12345 888 2
12346 111 5
12346 222 6
12346 333 2 ID Prob Cost Itm1 Cnt1 Itm2 Cnt2 Itm3 Cnt3 Itm4 Cnt4 Visible
--------------------------------------------------------------------
12345 100 50 555 3 666 5 777 20 888 2 1
12346 90 333 111 2 222 5 333 2 0 0 1So all needed I think is some simple loop with count of items from table 2 and add columns with count and item number to result. I've tried a lot of solutions but cannot find a suitable one.
SOLUTION : Please comment the solution if you have any solution for above query.
SOLUTION 1:
Solution provided by : Burak ÖZİŞ
Senior Systems Engineer DBA at DTG
DECLARE @Master TABLE (ID INT NOT NULL
, Prob INT NOT NULL
, Cost INT NOT NULL
, Visible BIT NOT NULL
);
DECLARE @Detail TABLE
( ID INT NOT NULL
, Item INT NOT NULL
, Count INT NOT NULL
);
-- YOUR TABLE
-- TABLE VALUES
INSERT @Master (ID, Prob, Cost, Visible)
VALUES (12345, 100, 50, 1)
, (12346, 90, 333, 1)
, (99999, 80, 222, 1);
INSERT @Detail (ID, Item, Count)
VALUES (12345, 555, 3)
, (12345, 666, 5)
, (12345, 777, 20)
, (12345, 888, 2)
, (12346, 111, 5)
, (12346, 222, 6)
, (12346, 333, 2)
, (99999, 100, 10)
, (99999, 200, 15)
, (99999, 300, 20)
, (99999, 400, 50)
, (99999, 500, 88)
, (99999, 600, 150);
-- TABLE VALUES
-- FINAL TABLE
CREATE TABLE LastTable
(ID INT NOT NULL
, Prob INT NOT NULL
, Cost INT NOT NULL
, Visible BIT NOT NULL
)
-- FINAL TABLE
-- PROCESS
DECLARE @sql nvarchar(MAX)
DECLARE @RealColumnCount INT = 0;
DECLARE @VirtualColumnCount INT = 0;
DECLARE @ID INT;
DECLARE @Prop INT;
DECLARE @Cost INT;
DECLARE @Visible INT;
DECLARE curOUT CURSOR for
SELECT M.ID, M.Prob, M.Cost, M.Visible FROM @Master M
OPEN curOUT
FETCH NEXT FROM curOUT INTO @ID, @Prop, @Cost, @Visible
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT LastTable (ID, Prob, Cost, Visible)
VALUES (@ID, @Prop, @Cost, @Visible);
SET @VirtualColumnCount = 0;
-- IN LOOP
DECLARE @ID2 INT;
DECLARE @Item INT;
DECLARE @Count INT;
DECLARE curIN CURSOR for
SELECT D.ID, D.Item, D.Count FROM @Detail D WHERE D.ID = @ID
OPEN curIN
FETCH NEXT FROM curIN INTO @ID2, @Item, @Count
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @VirtualColumnCount = @VirtualColumnCount + 1;
print CONCAT('Sanal: ', @VirtualColumnCount, ' - Gerçek: ', @RealColumnCount);
IF(@VirtualColumnCount > @RealColumnCount)
BEGIN
SET @RealColumnCount = @RealColumnCount + 1;
SET @sql = CONCAT('ALTER TABLE LastTable ADD Item', @RealColumnCount , ' INT NOT NULL DEFAULT(0); ALTER TABLE LastTable ADD Cnt', @RealColumnCount , ' INT NOT NULL DEFAULT(0); ');
EXEC(@sql)
END
SET @sql = CONCAT('UPDATE LastTable SET Item', @VirtualColumnCount , ' = ', @Item, ', Cnt', @VirtualColumnCount , ' = ', @Count, ' WHERE ID = ', @ID2);
EXEC(@sql)
FETCH NEXT FROM curIN INTO @ID2, @Item, @Count
END
CLOSE curIN
DEALLOCATE curIN
-- IN LOOP
FETCH NEXT FROM curOUT INTO @ID, @Prop, @Cost, @Visible
END
CLOSE curOUT
DEALLOCATE curOUT
-- PROCESS
-- TABLE LIST
SELECT * FROM LastTable
-- DROP THE TABLE
DROP TABLE LastTable
OUTPUT :
SOLUTION 2 (STATIC):
And Solution is here..
Solution provided by : Arjen
Senior Systems Engineer DBA at DTG
(https://www.linkedin.com/profile/view?id=74100236&authToken=null&locale=en_IN)DECLARE @table1 TABLE
(
ID smallint NOT NULL
, Prob smallint NOT NULL
, Cost smallint NOT NULL
, Visible bit NOT NULL
);
DECLARE @table2 TABLE
(
ID smallint NOT NULL
, Item1 smallint NOT NULL
, Count tinyint NOT NULL
);
INSERT @table1 (ID, Prob, Cost, Visible) VALUES
(12345, 100, 50, 1),
(12346, 90, 333, 1);
INSERT @table2 (ID, Item1, Count) VALUES
(12345, 555, 3),
(12345, 666, 5),
(12345, 777, 20),
(12345, 888, 2),
(12346, 111, 5),
(12346, 222, 6),
(12346, 333, 2);
SELECT
ID
, Prob
, Cost
, ISNULL(SUM(Itm1), 0) AS Itm1
, ISNULL(SUM(Cnt1), 0) AS Cnt1
, ISNULL(SUM(Itm2), 0) AS Itm2
, ISNULL(SUM(Cnt2), 0) AS Cnt2
, ISNULL(SUM(Itm3), 0) AS Itm3
, ISNULL(SUM(Cnt3), 0) AS Cnt3
, ISNULL(SUM(Itm4), 0) AS Itm4
, ISNULL(SUM(Cnt4), 0) AS Cnt4
, Visible
FROM
(
SELECT
t1.ID
, t1.Prob
, t1.Cost
, t1.Visible
, t2.Item1
, t2.Count
, 'Cnt' + CAST(ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t2.Item1) AS varchar(4)) AS rn_count
, 'Itm' + CAST(ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t2.Item1) AS varchar(4)) AS rn_item
FROM @table1 AS t1
JOIN @table2 AS t2 ON t1.ID = t2.ID
) AS j
PIVOT (SUM(Item1) FOR rn_item IN (Itm1, Itm2, Itm3, Itm4)) AS i
PIVOT (SUM(Count) FOR rn_count IN (Cnt1, Cnt2, Cnt3, Cnt4)) AS c
GROUP BY ID, Prob, Cost, Visible;
Output of above script :
Solution provided by : Arjen
ReplyDeleteDECLARE @table1 TABLE
(
ID smallint NOT NULL
, Prob smallint NOT NULL
, Cost smallint NOT NULL
, Visible bit NOT NULL
);
DECLARE @table2 TABLE
(
ID smallint NOT NULL
, Item1 smallint NOT NULL
, Count tinyint NOT NULL
);
INSERT @table1 (ID, Prob, Cost, Visible) VALUES
(12345, 100, 50, 1),
(12346, 90, 333, 1);
INSERT @table2 (ID, Item1, Count) VALUES
(12345, 555, 3),
(12345, 666, 5),
(12345, 777, 20),
(12345, 888, 2),
(12346, 111, 5),
(12346, 222, 6),
(12346, 333, 2);
SELECT
ID
, Prob
, Cost
, ISNULL(SUM(Itm1), 0) AS Itm1
, ISNULL(SUM(Cnt1), 0) AS Cnt1
, ISNULL(SUM(Itm2), 0) AS Itm2
, ISNULL(SUM(Cnt2), 0) AS Cnt2
, ISNULL(SUM(Itm3), 0) AS Itm3
, ISNULL(SUM(Cnt3), 0) AS Cnt3
, ISNULL(SUM(Itm4), 0) AS Itm4
, ISNULL(SUM(Cnt4), 0) AS Cnt4
, Visible
FROM
(
SELECT
t1.ID
, t1.Prob
, t1.Cost
, t1.Visible
, t2.Item1
, t2.Count
, 'Cnt' + CAST(ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t2.Item1) AS varchar(4)) AS rn_count
, 'Itm' + CAST(ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t2.Item1) AS varchar(4)) AS rn_item
FROM @table1 AS t1
JOIN @table2 AS t2 ON t1.ID = t2.ID
) AS j
PIVOT (SUM(Item1) FOR rn_item IN (Itm1, Itm2, Itm3, Itm4)) AS i
PIVOT (SUM(Count) FOR rn_count IN (Cnt1, Cnt2, Cnt3, Cnt4)) AS c
GROUP BY ID, Prob, Cost, Visible;
It is not so useful when new items will be added to the table. Try to use dynamic SQL adding columns in cycle into varchar variable, then exec(@query).
ReplyDeleteBurak ÖZİŞ has provided dynamic solution... Enjoy T-SQL
DeleteApologies for being blunt but if you use a Cursor or While loop to solve this problem, even in the presence of dynamic requirements, there's a pretty good chance that you won't get the job.
DeleteThis comment has been removed by the author.
ReplyDeleteHi,
DeleteI answer your question in Linkedin.
Here the solution:
-- YOUR TABLE
DECLARE @Master TABLE
(ID INT NOT NULL
, Prob INT NOT NULL
, Cost INT NOT NULL
, Visible BIT NOT NULL
);
DECLARE @Detail TABLE
( ID INT NOT NULL
, Item INT NOT NULL
, Count INT NOT NULL
);
-- YOUR TABLE
-- TABLE VALUES
INSERT @Master (ID, Prob, Cost, Visible)
VALUES (12345, 100, 50, 1)
, (12346, 90, 333, 1)
, (99999, 80, 222, 1);
INSERT @Detail (ID, Item, Count)
VALUES (12345, 555, 3)
, (12345, 666, 5)
, (12345, 777, 20)
, (12345, 888, 2)
, (12346, 111, 5)
, (12346, 222, 6)
, (12346, 333, 2)
, (99999, 100, 10)
, (99999, 200, 15)
, (99999, 300, 20)
, (99999, 400, 50)
, (99999, 500, 88)
, (99999, 600, 150);
-- TABLE VALUES
-- FINAL TABLE
CREATE TABLE LastTable
(ID INT NOT NULL
, Prob INT NOT NULL
, Cost INT NOT NULL
, Visible BIT NOT NULL
)
-- FINAL TABLE
-- PROCESS
DECLARE @sql nvarchar(MAX)
DECLARE @RealColumnCount INT = 0;
DECLARE @VirtualColumnCount INT = 0;
DECLARE @ID INT;
DECLARE @Prop INT;
DECLARE @Cost INT;
DECLARE @Visible INT;
DECLARE curOUT CURSOR for
SELECT M.ID, M.Prob, M.Cost, M.Visible FROM @Master M
OPEN curOUT
FETCH NEXT FROM curOUT INTO @ID, @Prop, @Cost, @Visible
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT LastTable (ID, Prob, Cost, Visible)
VALUES (@ID, @Prop, @Cost, @Visible);
SET @VirtualColumnCount = 0;
-- IN LOOP
DECLARE @ID2 INT;
DECLARE @Item INT;
DECLARE @Count INT;
DECLARE curIN CURSOR for
SELECT D.ID, D.Item, D.Count FROM @Detail D WHERE D.ID = @ID
OPEN curIN
FETCH NEXT FROM curIN INTO @ID2, @Item, @Count
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @VirtualColumnCount = @VirtualColumnCount + 1;
print CONCAT('Sanal: ', @VirtualColumnCount, ' - Gerçek: ', @RealColumnCount);
IF(@VirtualColumnCount > @RealColumnCount)
BEGIN
SET @RealColumnCount = @RealColumnCount + 1;
SET @sql = CONCAT('ALTER TABLE LastTable ADD Item', @RealColumnCount , ' INT NOT NULL DEFAULT(0); ALTER TABLE LastTable ADD Cnt', @RealColumnCount , ' INT NOT NULL DEFAULT(0); ');
EXEC(@sql)
END
SET @sql = CONCAT('UPDATE LastTable SET Item', @VirtualColumnCount , ' = ', @Item, ', Cnt', @VirtualColumnCount , ' = ', @Count, ' WHERE ID = ', @ID2);
EXEC(@sql)
FETCH NEXT FROM curIN INTO @ID2, @Item, @Count
END
CLOSE curIN
DEALLOCATE curIN
-- IN LOOP
FETCH NEXT FROM curOUT INTO @ID, @Prop, @Cost, @Visible
END
CLOSE curOUT
DEALLOCATE curOUT
-- PROCESS
-- TABLE LIST
SELECT * FROM LastTable
-- DROP THE TABLE
DROP TABLE LastTable
Great!! thanks for this dynamic solution....
DeleteHi, here is my solution :
ReplyDeleteDECLARE @ItemsColumnsCount INT
DECLARE @SqlCreateTable VARCHAR(MAX)
-- 1 : Create Table --------------------------------------------------------------
-- Get Max Items Count
SELECT @ItemsColumnsCount = MAX (ItemsCount) FROM (SELECT COUNT(Item1) ItemsCount FROM Table_2 GROUP BY ID) tbl1
-- Create Temp Table
SET @SqlCreateTable = 'CREATE TABLE ##TempTable (ID INT, Prob INT, Cost INT, Visible BIT, '
DECLARE @CurrentColumnIndx INT
SET @CurrentColumnIndx = 1
WHILE(@CurrentColumnIndx <= @ItemsColumnsCount)
BEGIN
SET @SqlCreateTable = @SqlCreateTable + 'Itm' + CONVERT(VARCHAR, @CurrentColumnIndx) + ' INT DEFAULT (0), Cnt'+ CONVERT(VARCHAR, @CurrentColumnIndx) + ' INT DEFAULT (0)'
IF(@CurrentColumnIndx <> @ItemsColumnsCount)
BEGIN
SET @SqlCreateTable = @SqlCreateTable + ', '
END
SET @CurrentColumnIndx = @CurrentColumnIndx + 1
END
SET @SqlCreateTable = @SqlCreateTable + ')'
EXEC (@SqlCreateTable)
-------------------------------------------------------------------------------
-- 2 : Loop using nested cursors to fill the temp table
-- Declare First Cursor to loop on the main table
DECLARE @ID INT
DECLARE @Prob INT
DECLARE @Cost INT
DECLARE @Visible BIT
DECLARE Main_Cursor CURSOR
FOR SELECT ID, Prob, Cost, Visible FROM Table_1
OPEN Main_Cursor
FETCH NEXT FROM Main_Cursor INTO @ID, @Prob, @Cost, @Visible
WHILE @@FETCH_STATUS = 0
BEGIN
-- Insert Main Data
INSERT INTO ##TempTable(ID, Prob, Cost, Visible) VALUES (@ID, @Prob, @Cost, @Visible)
-- Declare Second Cursor to loop on the items table
DECLARE @Item INT
DECLARE @Count INT
DECLARE @UpdateStatement VARCHAR(MAX)
DECLARE @ItemIndx INT
SET @ItemIndx = 0
DECLARE Sub_Cursor CURSOR
FOR SELECT Item1, Count FROM Table_2 WHERE ID = @ID
OPEN Sub_Cursor
FETCH NEXT FROM Sub_Cursor INTO @Item, @Count
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ItemIndx = @ItemIndx + 1
SET @UpdateStatement = 'UPDATE ##TempTable SET Itm' + CONVERT(VARCHAR, @ItemIndx) + '=' + CONVERT(VARCHAR, @Item)
+ ', Cnt' + CONVERT(VARCHAR, @ItemIndx) + '=' + CONVERT(VARCHAR, @Count)
+ ' WHERE ID = ' + CONVERT(VARCHAR, @ID)
EXEC (@UpdateStatement)
FETCH NEXT FROM Sub_Cursor INTO @Item, @Count
END
CLOSE Sub_Cursor;
DEALLOCATE Sub_Cursor;
--------------------------------------------------------------------
FETCH NEXT FROM Main_Cursor INTO @ID, @Prob, @Cost, @Visible
END
CLOSE Main_Cursor;
DEALLOCATE Main_Cursor;
SELECT * FROM ##TempTable
DROP TABLE ##TempTable
Thnx... for your solution...
Delete