none
70-761 Question RRS feed

  • Question

  • I need help to answer this question:

    You have a table named Cities that has the following two columns: CityID and CityName. The CityID column uses the
    int data type, and CityName uses nvarchar(max).
    You have a table named RawSurvey. Each row includes an identifier for a question and the number of persons that
    responded to that question from each of four cities. The table contains the following representative data:

    QuestonID

    Tokyo

    Boston

    London

    NewYork

    Q1

    1

    42

    48

    51

    Q2

    22

    39

    58

    42

    Q3

    29

    41

    61

    33

    Q4

    62

    70

    60

    50

    Q5

    63

    31

    41

    21

    Q6

    32

    1

    16

    34

    A reporting table named SurveyReport has the following columns: CityID, QuestionID, and RawCount, where RawCount
    is the value from the RawSurvey table.
    You need to write a Transact-SQL query to meet the following requirements:
    Retrieve data from the RawSurvey table in the format of the SurveyReport table.
    The CityID must contain the CityID of the city that was surveyed.
    The order of cities in all SELECT queries must match the order in the RawSurvey table.
    The order of cities in all IN statements must match the order in the RawSurvey table.
    Construct the query using the following guidelines:
    Use one-part names to reference tables and columns, except where not possible.
    ALL SELECT statements must specify columns.
    Do not use column or table aliases, except those provided.
    Do not surround object names with square brackets.


     
    • Edited by S Takeh Saturday, July 20, 2019 3:41 PM
    Saturday, July 20, 2019 3:14 PM

Answers

  • Hint, they are looking for "UNPIVOT".

    • Marked as answer by S Takeh Monday, July 22, 2019 5:52 AM
    Sunday, July 21, 2019 4:09 PM
    Moderator
  • Hi Takeh, 

    This is not difficult to achieve. You only need to use 'unpivot' and 'join' together.  

    If it helps, please kindly 'Mark' as answer. 

    IF OBJECT_ID('Cities') IS NOT NULL drop table Cities
    IF OBJECT_ID('RawSurvey') IS NOT NULL drop table RawSurvey
    
    create table Cities ( 
    CityID int,CityName nvarchar(max))
    create table RawSurvey ( 
    QuestonID varchar(5),Tokyo int,Boston int,London int,NewYork int) 
    
    
    insert into Cities values 
    (1,N'Tokyo'),(2,N'Boston'),(3,N'London'),(4,N'NewYork')
    insert into RawSurvey values 
    ('Q1',1,42,48,51),('Q2',22,39,58,42),('Q3',29,41,61,33),('Q4',62,70,60,50),
    ('Q5',63,31,41,21),('Q6',32,1,16,34)
    
    
    ;with cte as (
    select a.City, a.QuestonID,a.RawCount
    from RawSurvey s
    unpivot (RawCount for City in (Tokyo,Boston,London,NewYork))a) 
    
    select a.CityID, b.QuestonID,b.RawCount
    from Cities a 
    join cte b 
    on a.CityName= b.City
    
    /*
    CityID      QuestonID RawCount
    ----------- --------- -----------
    1           Q1        1
    2           Q1        42
    3           Q1        48
    4           Q1        51
    ...
    1           Q6        32
    2           Q6        1
    3           Q6        16
    4           Q6        34
    */

    Regards,

    Sabrina 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by S Takeh Monday, July 22, 2019 5:52 AM
    Monday, July 22, 2019 2:31 AM

All replies

  • Hint, they are looking for "UNPIVOT".

    • Marked as answer by S Takeh Monday, July 22, 2019 5:52 AM
    Sunday, July 21, 2019 4:09 PM
    Moderator
  • Hi Takeh, 

    This is not difficult to achieve. You only need to use 'unpivot' and 'join' together.  

    If it helps, please kindly 'Mark' as answer. 

    IF OBJECT_ID('Cities') IS NOT NULL drop table Cities
    IF OBJECT_ID('RawSurvey') IS NOT NULL drop table RawSurvey
    
    create table Cities ( 
    CityID int,CityName nvarchar(max))
    create table RawSurvey ( 
    QuestonID varchar(5),Tokyo int,Boston int,London int,NewYork int) 
    
    
    insert into Cities values 
    (1,N'Tokyo'),(2,N'Boston'),(3,N'London'),(4,N'NewYork')
    insert into RawSurvey values 
    ('Q1',1,42,48,51),('Q2',22,39,58,42),('Q3',29,41,61,33),('Q4',62,70,60,50),
    ('Q5',63,31,41,21),('Q6',32,1,16,34)
    
    
    ;with cte as (
    select a.City, a.QuestonID,a.RawCount
    from RawSurvey s
    unpivot (RawCount for City in (Tokyo,Boston,London,NewYork))a) 
    
    select a.CityID, b.QuestonID,b.RawCount
    from Cities a 
    join cte b 
    on a.CityName= b.City
    
    /*
    CityID      QuestonID RawCount
    ----------- --------- -----------
    1           Q1        1
    2           Q1        42
    3           Q1        48
    4           Q1        51
    ...
    1           Q6        32
    2           Q6        1
    3           Q6        16
    4           Q6        34
    */

    Regards,

    Sabrina 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by S Takeh Monday, July 22, 2019 5:52 AM
    Monday, July 22, 2019 2:31 AM
  • Thank you Sabrina
    Monday, July 22, 2019 5:53 AM
  • ;with mycte as (
    select QuestonID, cnt,city
    from RawSurvey s  
    CROSS APPLY (Values(Tokyo,'Tokyo'),(Boston,'Boston')
    ,(London,'London'),(NewYork,'NewYork')) d (cnt,city)
    )
    
    Select cityid,QuestonID, cnt
    from mycte m join cities c on m.city=c.CityName
    Order by cityid,QuestonID

    Monday, July 22, 2019 1:30 PM
    Moderator