none
split row query RRS feed

  • Question

  • Hi,

    create table rating_label
    (
    id_pk int,
    label_message varchar(500)
    )


    insert into rating_label values(105,'Look and Feel');
    insert into rating_label values(107,'Performance And Speed');
    insert into rating_label values(106,'Ease of Use');
    insert into rating_label values(109,'Good');
    insert into rating_label values(108,'Performance');

    create table query_info
    (
    cust_mob varchar(20),
    rating_id varchar(200),
    Rated_Date date default getdate()
    )


    Insert script

    INSERT into query_info (cust_mob,rating_id) VALUES (N'7756946507', N'109*4#108*0#107*5#106*1#105*5#', CAST(N'2018-02-12' AS Date))
    INSERT into query_info (cust_mob,rating_id)VALUES (N'6565676879', N'109*3#108*4#107*2#106*3#105*4#', CAST(N'2018-02-12' AS Date))
    INSERT into query_info (cust_mob,rating_id) VALUES (N'3434544657', N'109*2#108*1#107*0#106*2#105*0#', CAST(N'2018-02-12' AS Date))
    insert into query_info (cust_mob,rating_id)values('6556949494','109*1#108*1#107*3#106*0#105*4#')
    insert into query_info (cust_mob,rating_id)values('7556949486','109*1#108*2#107*2#106*0#105*0#')
    insert into query_info (cust_mob,rating_id)values('8547125934','109*5#108*0#107*4#106*0#105*4#')

    I need to Join the ID_PK column of rating_label table and 
    only before asterisk  of rating_id column from query_info table

    example

    109*1#108*1#107*3#106*0#105*4#

    here 

    109  is (Look and Feel) after asterisk is rating given by customer mobile numbers

    109 --> rating given is 1
    108 --> rating given is 1
    107 --> rating given is 3
    106 --> rating given is 0
    105 --> rating given is 4

    I want rating given for today date 
     
     Expected result





    label_message                        RATING_1      RATING_2       RATING_3      RATING_4     RATING_5

    Look and Feel(105)                        0                 0                       0              2                  0
    Performance And Speed(107)          0                1                       1             1                  0
    Ease of Use(106)                           0                 0                       0             0                  0             
    Good(109)                                    2                 0                       0            0                   1
    Performance(108)                         1                  1                       0            0                  0

    Thanks in advance

    Thursday, February 15, 2018 7:23 AM

All replies

  • see full illustration below

    your data

    create table rating_label
    (
    id_pk int,
    label_message varchar(500)
    )
    
    
    insert into rating_label values(105,'Look and Feel');
    insert into rating_label values(107,'Performance And Speed');
    insert into rating_label values(106,'Ease of Use');
    insert into rating_label values(109,'Good');
    insert into rating_label values(108,'Performance');
    
    create table query_info
    (
    cust_mob varchar(20),
    rating_id varchar(200),
    Rated_Date date default getdate()
    )
    
    
    
    
    INSERT into query_info (cust_mob,rating_id,Rated_date) VALUES (N'7756946507', N'109*4#108*0#107*5#106*1#105*5#', CAST(N'2018-02-12' AS Date))
    INSERT into query_info (cust_mob,rating_id,Rated_date)VALUES (N'6565676879', N'109*3#108*4#107*2#106*3#105*4#', CAST(N'2018-02-12' AS Date))
    INSERT into query_info (cust_mob,rating_id,Rated_date) VALUES (N'3434544657', N'109*2#108*1#107*0#106*2#105*0#', CAST(N'2018-02-12' AS Date))
    insert into query_info (cust_mob,rating_id,Rated_date)values('6556949494','109*1#108*1#107*3#106*0#105*4#', CAST(N'2018-02-15' AS Date))
    insert into query_info (cust_mob,rating_id,Rated_date)values('7556949486','109*1#108*2#107*2#106*0#105*0#', CAST(N'2018-02-15' AS Date))
    insert into query_info (cust_mob,rating_id,Rated_date)values('8547125934','109*5#108*0#107*4#106*0#105*4#', CAST(N'2018-02-15' AS Date))
    

    The solution

    IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #temp
    
    SELECT q.cust_mob,q.Rated_Date,CAST(PARSENAME(REPLACE(value,'*','.'),1) AS INT) AS Rating,CONCAT(r.label_message,'(',r.id_pk,')') AS Label ,
    ROW_NUMBER() OVER (PARTITION BY CONCAT(r.label_message,'(',r.id_pk,')') ORDER BY q.cust_mob) AS Seq 
    INTO #Temp
    FROM query_info q
    CROSS APPLY STRING_SPLIT(rating_id,'#')f
    JOIN rating_label r
    ON r.id_pk = PARSENAME(REPLACE(value,'*','.'),2)
    WHERE value > ''
    AND q.Rated_Date = CAST(GETDATE() AS DATE)
    
    
    DECLARE @ColumnList VARCHAR(5000),@SQL VARCHAR(MAX)
    
    SET @ColumnList = STUFF((SELECT DISTINCT ',[' +CAST( Seq AS VARCHAR(10)) + ']'
    FROM #Temp
    FOR XML PATH('')),1,1,'') 
    
    SET @SQL = 'SELECT *
    FROM (SELECT Rating,Label,Seq FROM #Temp)t
    PIVOT(SUM(Rating) FOR Seq IN(' + @ColumnList + '))p'
    
    EXEC(@SQL)

    The output


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, February 15, 2018 7:42 AM
  • Thanks for the query.i am getting below issue while executing the query

    Msg 208, Level 16, State 1, Line 6
    Invalid object name 'STRING_SPLIT'.

    version used

    Microsoft SQL Server 2012 - 11.0.5058.0 (X64)   May 14 2014 18:34:29   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) 

    Thursday, February 15, 2018 8:05 AM
  • Thanks for the query.i am getting below issue while executing the query

    Msg 208, Level 16, State 1, Line 6
    Invalid object name 'STRING_SPLIT'.

    version used

    Microsoft SQL Server 2012 - 11.0.5058.0 (X64)   May 14 2014 18:34:29   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) 

    ok in that case

    you've to use a string parsing udf

    like this

    https://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

    Create the above UDF first

    Then change your code as this

    IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #temp
    
    SELECT q.cust_mob,q.Rated_Date,CAST(PARSENAME(REPLACE(f.val,'*','.'),1) AS INT) AS Rating,CONCAT(r.label_message,'(',r.id_pk,')') AS Label ,
    ROW_NUMBER() OVER (PARTITION BY CONCAT(r.label_message,'(',r.id_pk,')') ORDER BY q.cust_mob) AS Seq 
    INTO #Temp
    FROM query_info q
    CROSS APPLY dbo.ParseValues(rating_id,'#')f
    JOIN rating_label r
    ON r.id_pk = PARSENAME(REPLACE(f.Val,'*','.'),2)
    WHERE f.Val > ''
    AND q.Rated_Date = CAST(GETDATE() AS DATE)
    
    
    DECLARE @ColumnList VARCHAR(5000),@SQL VARCHAR(MAX)
    
    SET @ColumnList = STUFF((SELECT DISTINCT ',[' +CAST( Seq AS VARCHAR(10)) + ']'
    FROM #Temp
    FOR XML PATH('')),1,1,'') 
    
    SET @SQL = 'SELECT *
    FROM (SELECT Rating,Label,Seq FROM #Temp)t
    PIVOT(SUM(Rating) FOR Seq IN(' + @ColumnList + '))p'
    
    EXEC(@SQL)

    and you will get the same output

    FYI STRING_SPLIT is only available from SQL 2016


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Thursday, February 15, 2018 8:11 AM
    Thursday, February 15, 2018 8:10 AM
  • STRING_SPLIT system function was introduced in SQL Server 2013

    As alternative

    CREATE TABLE Nums(n INT NOT NULL PRIMARY KEY)

    DECLARE @max AS INT, @rc AS INT

    SET @max = 8000
    SET @rc = 1

    BEGIN TRAN
      INSERT INTO Nums VALUES(1)

      WHILE @rc * 2 <= @max
      BEGIN
        INSERT INTO Nums
          SELECT n + @rc FROM Nums

        SET @rc = @rc * 2
      END

      INSERT INTO Nums
        SELECT n + @rc FROM Nums
        WHERE n + @rc <= @max
    COMMIT TRAN
    GO

    -- Create function
    CREATE FUNCTION fn_SeparateElements
      (@arr AS VARCHAR(7999)) RETURNS TABLE
    AS
    RETURN
      SELECT n - LEN(REPLACE(LEFT(arr, n), ',', '')) + 1 AS pos,
        SUBSTRING(@arr, n, CHARINDEX(',', @arr + ',', n) - n) AS element
      FROM (SELECT @arr AS arr) AS A JOIN Nums
        ON n <= LEN(@arr) AND SUBSTRING(',' + @arr, n, 1) = ','
    GO

    -- Test
    SELECT * FROM fn_SeparateElements('user1,user2,user3')

    pos  element
    ---- -------
    1    user1
    2    user2
    3    user3


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, February 15, 2018 8:14 AM
    Answerer
  • Thanks the given udf worked fine.

    i need to take below rating only,because these records belongs to todays date

    109*1#108*1#107*3#106*0#105*4#
    109*1#108*2#107*2#106*0#105*0#
    109*5#108*0#107*4#106*0#105*4#



    FOR LOOK and feel id is 105
    for 105 there are 2 rating for 4 so i required count as 2

    FOR Ease of Use id is 106
    for 106 there are 0 rating for so i required count as 0

    FOR Performance And Speed id is 107
    for 107 there are 3 , 2 and 1  rating for so i required count as 1 for rating 3 column, 1 for rating2 column, 1 in rating1 column

    LABEL                               R1      R2     R3   R4     R5

    Look and Feel                    0       0      0    2    0
    Performance And Speed    0    1      1    1   0
    Ease of Use                    0     0     0    0   0
    Good                            2       0     0    0   1
    Performance                     1    1     0    0   0

    Thanks in advance.

    Thursday, February 15, 2018 8:32 AM
  • Thanks the given udf worked fine.

    i need to take below rating only,because these records belongs to todays date

    109*1#108*1#107*3#106*0#105*4#
    109*1#108*2#107*2#106*0#105*0#
    109*5#108*0#107*4#106*0#105*4#



    FOR LOOK and feel id is 105
    for 105 there are 2 rating for 4 so i required count as 2

    FOR Ease of Use id is 106
    for 106 there are 0 rating for so i required count as 0

    FOR Performance And Speed id is 107
    for 107 there are 3 , 2 and 1  rating for so i required count as 1 for rating 3 column, 1 for rating2 column, 1 in rating1 column

    LABEL                               R1      R2     R3   R4     R5

    Look and Feel                    0       0      0    2    0
    Performance And Speed    0    1      1    1   0
    Ease of Use                    0     0     0    0   0
    Good                            2       0     0    0   1
    Performance                     1    1     0    0   0

    Thanks in advance.

    I've already included the logic for that in my code

    If you see my illustration its only taking rating the ratings from last three rows which are for todays date


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, February 15, 2018 8:38 AM
  • Thanks for the reply.

    sorry,Guess i am unable to make you understand very clearly

    I tried to explain below 

    Please consider only one below record

    INSERT query_info (cust_mob, rating_id, Rated_Date) VALUES (N'6556949494', N'109*1#108*1#107*3#106*0#105*4#', CAST(N'2018-02-15' AS Date))

    6556949494 109*1#108*1#107*3#106*0#105*4#  2018-02-15

    label_message                     R1    R2    R3   R4   R5

    Look and Feel                           0     0   0   1  0            ---> 105*4
    Performance And Speed            0     0   1   0  0              ---> 107*3
    Ease of Use                              0     0   0   0  0               ---> 106*0      
    Good                                       1     0   0   0  0                ----> 109*1 
    Performance                             1     0   0   0  0                --->108*1  

    Here 

    109*1#108*1#107*3#106*0#105*4#

    109*1 ->indicated Good -- *1 indicates he has given one star while rating in application,so i want to take the 
    count of that label message  given one star for good and record in R1 corresponding to GOOD R1 COLUMN

    105*4 ->indicated look and feel -- *4 indicates he has given 4 star while rating in application,so want to take the 
    count of that label message  given 4 stars for look and feel and record in R4 corresponding to look and feel R4 COLUMN

    Thanks in advance

    Thursday, February 15, 2018 9:46 AM
  • Thanks for the reply.

    sorry,Guess i am unable to make you understand very clearly

    I tried to explain below 

    Please consider only one below record

    INSERT query_info (cust_mob, rating_id, Rated_Date) VALUES (N'6556949494', N'109*1#108*1#107*3#106*0#105*4#', CAST(N'2018-02-15' AS Date))

    6556949494 109*1#108*1#107*3#106*0#105*4#  2018-02-15

    label_message                     R1    R2    R3   R4   R5

    Look and Feel                           0     0   0   1  0            ---> 105*4
    Performance And Speed            0     0   1   0  0              ---> 107*3
    Ease of Use                              0     0   0   0  0               ---> 106*0      
    Good                                       1     0   0   0  0                ----> 109*1 
    Performance                             1     0   0   0  0                --->108*1  

    Here 

    109*1#108*1#107*3#106*0#105*4#

    109*1 ->indicated Good -- *1 indicates he has given one star while rating in application,so i want to take the 
    count of that label message  given one star for good and record in R1 corresponding to GOOD R1 COLUMN

    105*4 ->indicated look and feel -- *4 indicates he has given 4 star while rating in application,so want to take the 
    count of that label message  given 4 stars for look and feel and record in R4 corresponding to look and feel R4 COLUMN

    Thanks in advance

    Ok

    Got your requirement

    here you go

    IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL DROP TABLE #temp1
    
    SELECT q.cust_mob,q.Rated_Date,CAST(PARSENAME(REPLACE(f.val,'*','.'),1) AS INT) AS Rating,CONCAT(r.label_message,'(',r.id_pk,')') AS Label ,
    ROW_NUMBER() OVER (PARTITION BY CONCAT(r.label_message,'(',r.id_pk,')') ORDER BY q.cust_mob) AS Seq 
    INTO #Temp1
    FROM query_info q
    CROSS APPLY dbo.ParseValues(rating_id,'#')f
    JOIN rating_label r
    ON r.id_pk = PARSENAME(REPLACE(f.Val,'*','.'),2)
    WHERE f.Val > ''
    AND CAST(PARSENAME(REPLACE(f.val,'*','.'),1) AS INT) > 0
    AND q.Rated_Date = CAST(GETDATE() AS DATE)
    
    
    
    
    SELECT Label,
    COALESCE([1],0) AS [1],
    COALESCE([2],0) AS [2],
    COALESCE([3],0) AS [3],
    COALESCE([4],0) AS [4],
    COALESCE([5],0) AS [5]
    FROM (SELECT r.Rating,Label,CASE WHEN t.Rating IS NOT NULL THEN 1 END AS Stat FROM #Temp1 t INNER JOIN (VALUES (1),(2),(3),(4),(5))r(rating) ON r.Rating = t.Rating)t
    PIVOT(SUM(Stat) FOR Rating IN([1],[2],[3],[4],[5]))p
    

    Output


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page



    Thursday, February 15, 2018 9:58 AM
  • Super and very awesome worked amazing.

    I am unable to find mark right answer option. I will find and mark for sure

    Thanks again

    Thursday, February 15, 2018 10:46 AM
  • Super and very awesome worked amazing.

    I am unable to find mark right answer option. I will find and mark for sure

    Thanks again

    You're welcome :)

    FYI you'll see a Mark As Answer link under each post. Thats what can be used to mark a response as answer


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Thursday, February 15, 2018 6:04 PM
    Thursday, February 15, 2018 10:53 AM
  •  
    create table rating_label
    (
    id_pk int,
    label_message varchar(500)
    )
    
    
    insert into rating_label values(105,'Look and Feel');
    insert into rating_label values(107,'Performance And Speed');
    insert into rating_label values(106,'Ease of Use');
    insert into rating_label values(109,'Good');
    insert into rating_label values(108,'Performance');
    
    create table query_info
    (
    cust_mob varchar(20),
    rating_id varchar(200),
    Rated_Date date default getdate()
    )
    
    INSERT query_info (cust_mob, rating_id, Rated_Date) VALUES (N'6556949494', N'109*1#108*1#107*3#106*0#105*4#', CAST(N'2018-02-15' AS Date))
     
    --INSERT into query_info (cust_mob,rating_id, Rated_Date) VALUES (N'7756946507', N'109*4#108*0#107*5#106*1#105*5#', CAST(N'2018-02-12' AS Date))
    --, (N'6565676879', N'109*3#108*4#107*2#106*3#105*4#', CAST(N'2018-02-12' AS Date))
    --, (N'3434544657', N'109*2#108*1#107*0#106*2#105*0#', CAST(N'2018-02-12' AS Date))
    --insert into query_info (cust_mob,rating_id)values('6556949494','109*1#108*1#107*3#106*0#105*4#'),('7556949486','109*1#108*2#107*2#106*0#105*0#'),('8547125934','109*5#108*0#107*4#106*0#105*4#')
    ;with mycte as (
    select * ,  
    CAST (N'<H><r>' +  REPLACE(rating_id,  '#', '</r><r>')+ '</r></H>' AS XML) vals
    from query_info
    WHERE Rated_Date='2018-02-15' )
    , mycte1 as
     ( 
     SELECT  cust_mob,  S.a.value('.', 'VARCHAR(100)') AS splitVal1 
     FROM mycte d
     CROSS APPLY d.[vals].nodes('/H/r') S(a)
     )
     , mycte2 as
     ( 
    Select *, CAST (N'<H><r>' + Replace( splitVal1, '*','</r><r>') + '</r></H>' AS XML) [vals] from mycte1
     )
     ,mycte3 as (
    
     Select DISTINCT cust_mob, 
     S.a.value('(/H/r)[1]', 'VARCHAR(100)') cat,  S.a.value('(/H/r)[2]', 'VARCHAR(100)') val  
     FROM mycte2 d   
     CROSS APPLY d.[vals].nodes('/H/r') S(a)  
     )
    
     Select rl.label_message, sum(Case when val=1 then 1 else 0 end) [1]
     , sum(Case when val=2 then 1 else 0 end) [2]
     , sum(Case when val=3 then 1 else 0 end) [3]
     , sum(Case when val=4 then 1 else 0 end) [4]
     , sum(Case when val=5 then 1 else 0 end) [5]
     from mycte3 m join rating_label rl on m.cat=rl.id_pk
     WHERE cat<>''
     Group by cat,rl.label_message
     Order by cat
      
     
    
     
    
    drop table query_info , rating_label

    Thursday, February 15, 2018 4:52 PM
    Moderator
  • You got some basic errors in your DDL. First of all, by definition, a table must have a key, but all your columns are nullable so it can never have a key. A table models a set of entities, so by its very nature it has to be plural or collective noun (do you really only have one label?), An identifier cannot be a numeric value because you do't do any math on it. The name of an identifier would never include metadata, such as your PK (this is called a Tibble) affix in the vague generic "id" that might have been your key.

    CREATE TABLE Something_Ratings
    (rating_code CHAR (5) NOT NULL PRIMARY KEY
     label_message VARCHAR (50) NOT NULL 
    );

    The ANSI/ISO standard table constructor syntax has been available for insert statements for quite a few years now. There is no reason to use the one row at a time syntax from the original Sybase product.

    INSERT INTO Something_Ratings
    VALUES
    ('105','Look and Feel'),
    ('107','Performance And Speed');
    ('106','Ease of Use');
    ('109','Good');
    ('108','Performance'),

    Frankly, I don't you put much thought into your encoding scheme. Have you ever been to a library and seen the Dewey Decimal Classification system? Did you notice that the term "performance" appears in two places? It's as if you just put things off paper forms, did no editing or design work and made a list of the raw text.

    Did you know that phone numbers have an international standard? It is fixed length and only goes to 15 characters.

    CREATE TABLE Query_Info
    (cust_mobile CHAR (15) NOT NULL,
     rating_code CHAR (5) NOT NULL
     REFERENCES Something_Ratings (rating_code),
     PRIMARY KEY (cust_mobile, rating_code),
     rating_score INTEGER  DEFAULT 0 NOT NULL 
      CHECK( rating_score BETWEEN 0 and 5),
     rated_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);

    Please look at the constraints and the references that are part of the DDL. Notice that all the columns are scalar values, and not weirdly punctuated lists. This is part of what is called first normal form in RDBMS.

    INSERT INTO Query_info (cust_mob, rating_code, rating_score, rated_date)
     VALUES 
    ('7756946507', '109', 4, '2018-02-12' ),
    ('7756946507', '108', 0, '2018-02-12' ),
    ('7756946507', '107', 5, '2018-02-12' ),
    ('7756946507', '106, 1, '2018-02-12' ),
    ('7756946507', '105, 5, '2018-02-12' ),
    etc.
     



    ('6565676879', '109*3#108*4#107*2#106*3#105*4#', '2018-02-12' ),
    ('3434544657', '109*2#108*1#107*0#106*2#105*0#', '2018-02-12' ),
    ('6556949494','109*1#108*1#107*3#106*0#105*4#'),
    ('7556949486','109*1#108*2#107*2#106*0#105*0#'),
    ('8547125934','109*5#108*0#107*4#106*0#105*4#');

    >> I need to Join the ID_PK [sic] column of rating_label table and only before asterisk of rating_id column from query_info table. <<

    No you do not need to do this. You need read a book on basic RDBMS and learn what normalization is. You're trying to write a parser and invent own language on the fly instead of following good practices.

    example

    109*1#108*1#107*3#106*0#105*4#

    here 

    109 is (Look and Feel) after asterisk is rating given by customer mobile numbers

    109 --> rating given is 1
    108 --> rating given is 1
    107 --> rating given is 3
    106 --> rating given is 0
    105 --> rating given is 4

    I want rating given for today date 
     
     Expected result





    label_message            RATING_1   RATING_2    RATING_3   RATING_4   RATING_5

    Look and Feel(105)            0         0            0       2         0
    Performance And Speed(107)     0        1            1       1         0
    Ease of Use(106)              0         0            0       0         0       
    Good(109)                  2         0            0      0          1
    Performance(108)             1         1            0      0         0
    Tha

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, February 15, 2018 7:58 PM