none
Query to get sets of 3 values RRS feed

  • Question

  • Am trying to get number of occurrence of any 3 set of values from the table.


    Create table [User_information]
    (
    ID INT IDENTITY (1,1),
    [Day] Varchar(10),
    User1 Varchar(10),
    User2 Varchar(10),
    User3 Varchar(10),
    User4 Varchar(10),
    User5 Varchar(10)
    )


    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day1' , 'a','b','c','d','e')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day2' , 'g','j','c','k','m')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day3' , 'o','l','g','k','s')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day4' , 'e','t','c','m','x')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day5' , 'j','k','c','d','m')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day6' , 'g','e','h','r','p')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day7' , 'z','b','a','m','c')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day8' , 'a','r','z','b','j')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day9' , 'q','c','r','m','x')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day10' , 'z','a','b','i','w')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day11' , 't','d','s','n','y')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day12' , 't','c','v','m','x')

    For every day I need to get 3 sets from 5 values without any repetition and compare it with next days.

    Example in Day1 i get these 3 sets  below

    {'a','b','c'} {'a','b','d'} {'a','b','e'} {'a','c','b'} {'a','c','d'} {'a','c','e'} {'a','d','b'} {'a','d','c'} {'a','d','e'} {'a','e','b'} {'a','e','c'} {'a','e','d'} {'b','a','c'} {'b','a','d'} {'b','a','e'} {'b','c','a'} {'b','c','d'} {'b','c','e'} {'b','d','a'} {'b','d','c'} {'b','d','e'} {'b','e','a'} {'b','e','c'} {'b','e','d'} {'c','a','b'} {'c','a','d'} {'c','a','e'} {'c','b','a'} {'c','b','d'} {'c','b','e'} {'c','d','a'} {'c','d','b'} {'c','d','e'} {'c','e','a'} {'c','e','b'} {'c','e','d'} {'d','a','b'} {'d','a','c'} {'d','a','e'} {'d','b','a'} {'d','b','c'} {'d','b','e'} {'d','c','a'} {'d','c','b'} {'d','c','e'} {'d','e','a'} {'d','e','b'} {'d','e','c'} {'e','a','b'} {'e','a','c'} {'e','a','d'} {'e','b','a'} {'e','b','c'} {'e','b','d'} {'e','c','a'} {'e','c','b'} {'e','c','d'} {'e','d','a'} {'e','d','b'} {'e','d','c'}

    Then I need to identify the number of occurrence of these sets in next days.

    {'a','b','c'} - 2
    {'a','b','d'} - 1
    {'a','b','e'} - 2
    {'a','c','b'} - 2

    and so on.....

    Like this i need to do for every row..

    Saturday, June 8, 2019 1:40 AM

Answers

  • Good day Mu,

    Assuming I understood your request, the following should solve your need;

    ;With MyCTE as (
    	select ID, [Day] d, txt
    	from [User_information] s
    	unpivot(txt for subject in (User1,User2,User3,User4,User5)) u
    	--where id = 1
    )
    , MyCTE2 as (
    	SELECT t1.ID, t1.d, t1.txt c1, t2.txt c2 , t3.txt c3
    	FROM MyCTE t1
    -- Note! you can use CROSS JOIN instead and add filter using WHERE INNER JOIN MyCTE t2 ON t1.ID=t2.ID and t1.d = t2.d and t1.txt<>t2.txt INNER JOIN MyCTE t3 ON t1.ID=t3.ID and t1.d = t3.d and t1.txt<>t3.txt and t2.txt<>t3.txt --order by t1.ID, t1.d, t1.txt,t2.txt,t3.txt ) SELECT ID, d, c1, c2 , c3, [Count] = COUNT(*) OVER(PARTITION BY c1, c2 , c3) from MyCTE2 order by ID, d, c1, c2 , c3
     


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Sunday, June 9, 2019 4:47 AM
    Moderator
  • Hi MU DBA,

     

    Thank you for your issue.

     

    As you said that  'identify the number of occurrence of these sets in next days' , I think  the number of 'DAY*' is bigger and the number in your expected result might be bigger.

     

    So I do a little change in pituach's advice and provide you  dynamic code. Please try following script .

     
    IF OBJECT_ID('User_information') IS NOT NULL drop table  [User_information]
    go 
    Create table [User_information]
    (
    ID INT IDENTITY (1,1),
    [Day] Varchar(10),
    User1 Varchar(10),
    User2 Varchar(10),
    User3 Varchar(10),
    User4 Varchar(10),
    User5 Varchar(10)
    )
    
    
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day1' , 'a','b','c','d','e')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day2' , 'g','j','c','k','m')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day3' , 'o','l','g','k','s')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day4' , 'e','t','c','m','x')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day5' , 'j','k','c','d','m')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day6' , 'g','e','h','r','p')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day7' , 'z','b','a','m','c')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day8' , 'a','r','z','b','j')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day9' , 'q','c','r','m','x')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day10' , 'z','a','b','i','w')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day11' , 't','d','s','n','y')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day12' , 't','c','v','m','x')
    
    
    ;With MyCTE as (
    	select ID, [Day] , txt
    	from [User_information] s
    	unpivot(txt for subject in (User1,User2,User3,User4,User5)) u
    )
    , MyCTE2 as (
    	SELECT t1.ID, t1.[Day], t1.txt c1, t2.txt c2 , t3.txt c3
    	FROM MyCTE t1
    	INNER JOIN MyCTE t2 ON t1.ID=t2.ID and t1.[Day] = t2.[Day] and t1.txt<>t2.txt
    	INNER JOIN MyCTE t3 ON t1.ID=t3.ID and t1.[Day] = t3.[Day] and t1.txt<>t3.txt and t2.txt<>t3.txt
    )
    SELECT ID, [Day], c1, c2 , c3, 
    [Count] = COUNT(*) OVER(PARTITION BY c1, c2 , c3 order by cast(substring([Day],4,len([Day])-3) as int) desc)
    from MyCTE2
    order by ID, [Day], c1, c2 , c3
    
    
    
    ------dynamic-------------
    DECLARE @ColNames varchar(1000);
    SET @ColNames = '';
    SELECT @ColNames =stuff((
        SELECT DISTINCT ',' + QUOTENAME(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS p2
        WHERE TABLE_NAME = 'User_information'
    	  AND COLUMN_NAME like 'User%'
        FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
                ,1,1,'')
    DECLARE @CMD nvarchar(2000);
    SET @CMD = ';With MyCTE as (
    	select ID, [Day] , txt
    	from [User_information] s
    	unpivot(txt for subject in (' + 
               +@ColNames + ')) u
    )
    , MyCTE2 as (
    	SELECT t1.ID, t1.[Day], t1.txt c1, t2.txt c2 , t3.txt c3
    	FROM MyCTE t1
    	INNER JOIN MyCTE t2 ON t1.ID=t2.ID and t1.[Day] = t2.[Day] and t1.txt<>t2.txt
    	INNER JOIN MyCTE t3 ON t1.ID=t3.ID and t1.[Day] = t3.[Day] and t1.txt<>t3.txt and t2.txt<>t3.txt
    )
    SELECT ID, [Day], c1, c2 , c3, 
    [Count] = COUNT(*) OVER(PARTITION BY c1, c2 , c3 order by cast(substring([Day],4,len([Day])-3) as int) desc)
    from MyCTE2
    order by ID, [Day], c1, c2 , c3'
    ----PRINT @CMD
    execute sp_executesql @CMD
    
    

    Hope it can help you.

     

    Best Regards,

    Rachel


    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.

    Monday, June 10, 2019 6:20 AM

All replies

  • Good day Mu,

    Assuming I understood your request, the following should solve your need;

    ;With MyCTE as (
    	select ID, [Day] d, txt
    	from [User_information] s
    	unpivot(txt for subject in (User1,User2,User3,User4,User5)) u
    	--where id = 1
    )
    , MyCTE2 as (
    	SELECT t1.ID, t1.d, t1.txt c1, t2.txt c2 , t3.txt c3
    	FROM MyCTE t1
    -- Note! you can use CROSS JOIN instead and add filter using WHERE INNER JOIN MyCTE t2 ON t1.ID=t2.ID and t1.d = t2.d and t1.txt<>t2.txt INNER JOIN MyCTE t3 ON t1.ID=t3.ID and t1.d = t3.d and t1.txt<>t3.txt and t2.txt<>t3.txt --order by t1.ID, t1.d, t1.txt,t2.txt,t3.txt ) SELECT ID, d, c1, c2 , c3, [Count] = COUNT(*) OVER(PARTITION BY c1, c2 , c3) from MyCTE2 order by ID, d, c1, c2 , c3
     


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Sunday, June 9, 2019 4:47 AM
    Moderator
  • Hi MU DBA,

     

    Thank you for your issue.

     

    As you said that  'identify the number of occurrence of these sets in next days' , I think  the number of 'DAY*' is bigger and the number in your expected result might be bigger.

     

    So I do a little change in pituach's advice and provide you  dynamic code. Please try following script .

     
    IF OBJECT_ID('User_information') IS NOT NULL drop table  [User_information]
    go 
    Create table [User_information]
    (
    ID INT IDENTITY (1,1),
    [Day] Varchar(10),
    User1 Varchar(10),
    User2 Varchar(10),
    User3 Varchar(10),
    User4 Varchar(10),
    User5 Varchar(10)
    )
    
    
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day1' , 'a','b','c','d','e')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day2' , 'g','j','c','k','m')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day3' , 'o','l','g','k','s')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day4' , 'e','t','c','m','x')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day5' , 'j','k','c','d','m')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day6' , 'g','e','h','r','p')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day7' , 'z','b','a','m','c')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day8' , 'a','r','z','b','j')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day9' , 'q','c','r','m','x')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day10' , 'z','a','b','i','w')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day11' , 't','d','s','n','y')
    Insert into [User_information] ([Day],User1,User2,User3,User4,User5) Values ('Day12' , 't','c','v','m','x')
    
    
    ;With MyCTE as (
    	select ID, [Day] , txt
    	from [User_information] s
    	unpivot(txt for subject in (User1,User2,User3,User4,User5)) u
    )
    , MyCTE2 as (
    	SELECT t1.ID, t1.[Day], t1.txt c1, t2.txt c2 , t3.txt c3
    	FROM MyCTE t1
    	INNER JOIN MyCTE t2 ON t1.ID=t2.ID and t1.[Day] = t2.[Day] and t1.txt<>t2.txt
    	INNER JOIN MyCTE t3 ON t1.ID=t3.ID and t1.[Day] = t3.[Day] and t1.txt<>t3.txt and t2.txt<>t3.txt
    )
    SELECT ID, [Day], c1, c2 , c3, 
    [Count] = COUNT(*) OVER(PARTITION BY c1, c2 , c3 order by cast(substring([Day],4,len([Day])-3) as int) desc)
    from MyCTE2
    order by ID, [Day], c1, c2 , c3
    
    
    
    ------dynamic-------------
    DECLARE @ColNames varchar(1000);
    SET @ColNames = '';
    SELECT @ColNames =stuff((
        SELECT DISTINCT ',' + QUOTENAME(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS p2
        WHERE TABLE_NAME = 'User_information'
    	  AND COLUMN_NAME like 'User%'
        FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
                ,1,1,'')
    DECLARE @CMD nvarchar(2000);
    SET @CMD = ';With MyCTE as (
    	select ID, [Day] , txt
    	from [User_information] s
    	unpivot(txt for subject in (' + 
               +@ColNames + ')) u
    )
    , MyCTE2 as (
    	SELECT t1.ID, t1.[Day], t1.txt c1, t2.txt c2 , t3.txt c3
    	FROM MyCTE t1
    	INNER JOIN MyCTE t2 ON t1.ID=t2.ID and t1.[Day] = t2.[Day] and t1.txt<>t2.txt
    	INNER JOIN MyCTE t3 ON t1.ID=t3.ID and t1.[Day] = t3.[Day] and t1.txt<>t3.txt and t2.txt<>t3.txt
    )
    SELECT ID, [Day], c1, c2 , c3, 
    [Count] = COUNT(*) OVER(PARTITION BY c1, c2 , c3 order by cast(substring([Day],4,len([Day])-3) as int) desc)
    from MyCTE2
    order by ID, [Day], c1, c2 , c3'
    ----PRINT @CMD
    execute sp_executesql @CMD
    
    

    Hope it can help you.

     

    Best Regards,

    Rachel


    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.

    Monday, June 10, 2019 6:20 AM
  • It worked like a charm..

    • Edited by MU DBA Saturday, June 15, 2019 1:13 AM
    Saturday, June 15, 2019 1:12 AM
  • Thanks for the upgrade
    Saturday, June 15, 2019 1:13 AM
  • You are welcome 👍

    I am glad to hear


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, June 15, 2019 4:15 AM
    Moderator