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

);

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


 

Related

SQL QUERY QUIZ 5756260236295353245

Post a comment

  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

emo-but-icon

Follow Us

Recent

Comments

Donate

Side Ads

Text Widget

Connect Us

Sql Server 2017 Interview Questions
Sql Server Basic Interview Query Set-1
Sql Server Basic Interview Query Set-2
Sql Server Date-Time Interview Query SET-3
Sql Server Salary Interview Query SET-4
Sql Server Group By Interview Query SET-5
Sql Server Join Interview Query SET-6
Sql Server Tricky Join Interview Query SET-7
Sql Server DDL Interview Query SET-8
Small but very Tricky Sql Server Query SET-9
Very Much Tricky Query (not 4 fresher)SET-10
Sql Server Complex Interview Query SET-11
Sql Server Datatype Interview Questions
Sql Server View Interview Questions
Sql Server Index Interview Questions
TCS Tricky Sql Interview Queries
HCL Sql Interview Queries
Sql Server SP Interview Questions
Sql Server Trigger Interview Questions
Sql Server Temp Table Interview Questions
Sql Server 2016 Interview Questions
Sql Server Performance Tuning Interview Q.
Sql Server Constraints Interview Questions
Sql Server Storage/Size Interview Questions
Sql Server Very Basic Interview Questions
Sql Server Quiz for Fresher
Sql Server Icon Quiz for All
Sql Server 300+ Theoretical Interview Questions
Sql Server Complete Set

ASP.NET AND JQUERY INTERVIEW QUESTION
ASP.NET: Web Config Interview Question
ASP.NET: View State Interview Question
ASP.NET: Session Interview Question
ASP.NET: Session Interview Question 
ASP.NET: Security Interview Question
ASP.NET: Catching Interview Question
C# OOPS Interview Question
MORE Interview Question
JQUERY: Interview Question Set-1
JQUERY: Interview Question Set-2
JAVA-SCRIPT: Interview Question Set-1
JAVA-SCRIPT: Interview Question Set-2
ASP.NET MVC: Interview Question Set-1

POPULAR POSTS
10 MOST DIFFICULT INTERVIEW QUESTIONS
5 BEST INTERVIEW TIPS
YOUR 10 MISTAKE DURING INTERVIEW
2016 TOP 20 GROUP DISCUSSION TOPICS
HOW SQL PERFORMANCE AFFECTED BY DATATYPE
item