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

);

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


 

Comments

  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;

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

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

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

      Delete
  3. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Hi,
      I 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

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

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

    ReplyDelete

Contact Form

Send