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 ------------------...

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   1``````

Table2:
`````` ID     Item1    Count
-----------------------
12345    555       3
12345    666       5
12345    777       20
12345    888       2
12346    111       5
12346    222       6
12346    333       2``````

I need to make output like this:
`````` 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      1``````

So 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

);

-- 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

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 :

Related

SQL QUERY QUIZ 5756260236295353245

1. Solution provided by : Arjen

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;

2. 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).

1. Burak ÖZİŞ has provided dynamic solution... Enjoy T-SQL

2. Apologies 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.

3. This comment has been removed by the author.

1. Hi,

Here the solution:

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
);

-- 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

2. Great!! thanks for this dynamic solution....

4. Hi, here is my solution :

DECLARE @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 @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)

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