locked
SQL Query question RRS feed

  • Question

  • Hello,

    USE [tempdb]
    GO
    
    IF OBJECT_ID('tempdb..#MD') IS NOT NULL
    	DROP TABLE #MD
    
    CREATE TABLE #MD
    (
    	MID		INT,
    	CLASS	CHAR(1)
    )
    
    INSERT INTO #MD VALUES ( 1, 'D' )
    INSERT INTO #MD VALUES ( 1, 'M' )
    
    INSERT INTO #MD VALUES ( 2, 'D' )
    INSERT INTO #MD VALUES ( 2, 'M' )
    INSERT INTO #MD VALUES ( 2, 'V' )
    
    INSERT INTO #MD VALUES ( 3, 'D' )
    INSERT INTO #MD VALUES ( 3, 'V' )
    
    SELECT	MID, CLASS
    FROM	#MD
    
    IF OBJECT_ID('tempdb..#MD') IS NOT NULL
    	DROP TABLE #MD


    Expected o/p:-

    SELECT	'1' AS 'MID', 'D' AS 'CLASS', '110' AS 'BKEY'
    UNION ALL
    SELECT	'1' AS 'MID', 'M' AS 'CLASS', '110' AS 'BKEY'
    UNION ALL
    SELECT	'2' AS 'MID', 'D' AS 'CLASS', '111' AS 'BKEY'
    UNION ALL
    SELECT	'2' AS 'MID', 'M' AS 'CLASS', '111' AS 'BKEY'
    UNION ALL
    SELECT	'2' AS 'MID', 'V' AS 'CLASS', '111' AS 'BKEY'
    UNION ALL
    SELECT	'3' AS 'MID', 'D' AS 'CLASS', '101' AS 'BKEY'
    UNION ALL
    SELECT	'3' AS 'MID', 'V' AS 'CLASS', '101' AS 'BKEY'


    Here are my business rules:-

    CASE
    	WHEN CLASS.D = 1 AND CLASS.M = 0 AND CLASS.V = 0 THEN '100'
    	WHEN CLASS.D = 0 AND CLASS.M = 1 AND CLASS.V = 0 THEN '010'
    	WHEN CLASS.D = 0 AND CLASS.M = 0 AND CLASS.V = 1 THEN '001'
    	WHEN CLASS.D = 1 AND CLASS.M = 1 AND CLASS.V = 0 THEN '110'
    	WHEN CLASS.D = 1 AND CLASS.M = 1 AND CLASS.V = 1 THEN '111'
    	WHEN CLASS.D = 1 AND CLASS.M = 0 AND CLASS.V = 1 THEN '101'
    	WHEN CLASS.D = 0 AND CLASS.M = 1 AND CLASS.V = 1 THEN '011'
    END


    Please help with the BKEY values.

    Thanks

    Regards,

    gk03


    Please do let us know your feedback. Thank You - KG, MCTS

    Tuesday, September 19, 2017 8:10 PM

Answers

  • Hi,

    Please check this out, see if it works for you:

    CREATE TABLE #MD
    (
    	MID		INT,
    	CLASS	CHAR(1)
    )
    
    INSERT INTO #MD VALUES ( 1, 'D' )
    INSERT INTO #MD VALUES ( 1, 'M' )
    
    INSERT INTO #MD VALUES ( 2, 'D' )
    INSERT INTO #MD VALUES ( 2, 'M' )
    INSERT INTO #MD VALUES ( 2, 'V' )
    
    INSERT INTO #MD VALUES ( 3, 'D' )
    INSERT INTO #MD VALUES ( 3, 'V' )
    
    SELECT	MID, CLASS
    FROM	#MD
    
    select a.*,
    Case When b.CLASS='D' then '100' 
    	When b.CLASS='M' then '010'
    	When b.CLASS='V' then '001'
    	When b.CLASS='D,M' then '110'
    	When b.CLASS='D,M,V' then '111'
    	When b.CLASS='D,V' then '101'
    	When b.CLASS='M,V' then '011' Else '' End as BKEY
    from #MD a
    inner join (
    select distinct MID,LEFT(CLASS,LEN(CLASS)-1) as CLASS
    from (
    	select MID,(
    		select CLASS+ ',' as [text()]
    		from #MD t1
    		where t1.MID=t2.MID
    		order by t1.MID,t1.CLASS
    		for xml Path('')) CLASS
    	from #MD t2)t ) b on a.MID=b.MID
    

    Thanks,
    Xi Jin.


    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 gk1393 Wednesday, September 20, 2017 8:56 PM
    Wednesday, September 20, 2017 3:56 AM

All replies

  • If you are looking for the column "BKEY" automatically hold the data based on the "Mid" and "class" columns,The below link could help you.

    https://www.mssqltips.com/sqlservertip/2481/getting-creative-with-computed-columns-in-sql-server/


    Tuesday, September 19, 2017 8:23 PM
  • No we don't want to hold within database with the Computed column concept, we want to generate it during run time with the my business case conditions.

    Please do let us know your feedback. Thank You - KG, MCTS

    Tuesday, September 19, 2017 8:25 PM
  • IF OBJECT_ID('tempdb..#MD') IS NOT NULL
    	DROP TABLE #MD
    
    CREATE TABLE #MD
    (
    	MID		INT,
    	CLASS	CHAR(1)
    )
    
    INSERT INTO #MD VALUES ( 1, 'D' )
    INSERT INTO #MD VALUES ( 1, 'M' )
    
    INSERT INTO #MD VALUES ( 2, 'D' )
    INSERT INTO #MD VALUES ( 2, 'M' )
    INSERT INTO #MD VALUES ( 2, 'V' )
    
    INSERT INTO #MD VALUES ( 3, 'D' )
    INSERT INTO #MD VALUES ( 3, 'V' )
    
    ;with mycte as (
    SELECT	MID,  
    	Max(CASE WHEN CLASS='D' then 1 Else 0 END) D,
    	Max(CASE WHEN CLASS='V' then  1 Else 0 END) V,
    	Max(CASE WHEN CLASS='M' then  1 Else 0 END) M
    	 
    	  
     FROM	#MD  
      Group by  MID
      )
    		
    		
    		Select md.MID,md.CLASS,
    		CASE
    	WHEN CLASS.D = 1 AND CLASS.M = 0 AND CLASS.V = 0 THEN '100'
    	WHEN CLASS.D = 0 AND CLASS.M = 1 AND CLASS.V = 0 THEN '010'
    	WHEN CLASS.D = 0 AND CLASS.M = 0 AND CLASS.V = 1 THEN '001'
    	WHEN CLASS.D = 1 AND CLASS.M = 1 AND CLASS.V = 0 THEN '110'
    	WHEN CLASS.D = 1 AND CLASS.M = 1 AND CLASS.V = 1 THEN '111'
    	WHEN CLASS.D = 1 AND CLASS.M = 0 AND CLASS.V = 1 THEN '101'
    	WHEN CLASS.D = 0 AND CLASS.M = 1 AND CLASS.V = 1 THEN '011'
    END
    
    from mycte	CLASS	join 	#MD md on  CLASS.MID=md.MID
    
    
     
    
    IF OBJECT_ID('tempdb..#MD') IS NOT NULL
    	DROP TABLE #MD
    

    Tuesday, September 19, 2017 8:27 PM
  • We're having performance issue with temp table & CTE since MD is huge table.

    I was trying to do something like this in a very simple way but unable to get it.

    SELECT	a.MID, a.CLASS
    		, (
    			SELECT	CASE
    						WHEN a.MID = b.MID AND b.CLASS = 'D' AND b.CLASS != 'M' AND b.CLASS != 'V' THEN '100'
    						WHEN a.MID = b.MID AND b.CLASS != 'D' AND b.CLASS = 'M' AND b.CLASS != 'V' THEN '010'
    						WHEN a.MID = b.MID AND b.CLASS != 'D' AND b.CLASS != 'M' AND b.CLASS = 'V' THEN '001'
    						WHEN a.MID = b.MID AND b.CLASS = 'D' AND b.CLASS = 'M' AND b.CLASS != 'V' THEN '110'
    						WHEN a.MID = b.MID AND b.CLASS = 'D' AND b.CLASS = 'M' AND b.CLASS = 'V' THEN '111'
    						WHEN a.MID = b.MID AND b.CLASS = 'D' AND b.CLASS != 'M' AND b.CLASS = 'V' THEN '101'
    						WHEN a.MID = b.MID AND b.CLASS != 'D' AND b.CLASS = 'M' AND b.CLASS = 'V' THEN '011'
    						ELSE ''
    					END AS BKEY
    			FROM	#MD b
    		) AS BKEY
    FROM	#MD a

    Thanks


    Please do let us know your feedback. Thank You - KG, MCTS

    Tuesday, September 19, 2017 8:43 PM
  • Hi,

    Please check this out, see if it works for you:

    CREATE TABLE #MD
    (
    	MID		INT,
    	CLASS	CHAR(1)
    )
    
    INSERT INTO #MD VALUES ( 1, 'D' )
    INSERT INTO #MD VALUES ( 1, 'M' )
    
    INSERT INTO #MD VALUES ( 2, 'D' )
    INSERT INTO #MD VALUES ( 2, 'M' )
    INSERT INTO #MD VALUES ( 2, 'V' )
    
    INSERT INTO #MD VALUES ( 3, 'D' )
    INSERT INTO #MD VALUES ( 3, 'V' )
    
    SELECT	MID, CLASS
    FROM	#MD
    
    select a.*,
    Case When b.CLASS='D' then '100' 
    	When b.CLASS='M' then '010'
    	When b.CLASS='V' then '001'
    	When b.CLASS='D,M' then '110'
    	When b.CLASS='D,M,V' then '111'
    	When b.CLASS='D,V' then '101'
    	When b.CLASS='M,V' then '011' Else '' End as BKEY
    from #MD a
    inner join (
    select distinct MID,LEFT(CLASS,LEN(CLASS)-1) as CLASS
    from (
    	select MID,(
    		select CLASS+ ',' as [text()]
    		from #MD t1
    		where t1.MID=t2.MID
    		order by t1.MID,t1.CLASS
    		for xml Path('')) CLASS
    	from #MD t2)t ) b on a.MID=b.MID
    

    Thanks,
    Xi Jin.


    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 gk1393 Wednesday, September 20, 2017 8:56 PM
    Wednesday, September 20, 2017 3:56 AM
  • Please check below;

    --USE [tempdb]
    --GO
    
    --IF OBJECT_ID('tempdb..#MD') IS NOT NULL
    --	DROP TABLE #MD
    
    --CREATE TABLE #MD
    --(
    --	MID		INT,
    --	CLASS	CHAR(1)
    --)
    
    --INSERT INTO #MD VALUES ( 1, 'D' )
    --INSERT INTO #MD VALUES ( 1, 'M' )
    
    --INSERT INTO #MD VALUES ( 2, 'D' )
    --INSERT INTO #MD VALUES ( 2, 'M' )
    --INSERT INTO #MD VALUES ( 2, 'V' )
    
    --INSERT INTO #MD VALUES ( 3, 'D' )
    --INSERT INTO #MD VALUES ( 3, 'V' )
    
    --INSERT INTO #MD VALUES ( 4, 'M' )
    --INSERT INTO #MD VALUES ( 4, 'V' )
    
    
    ; with wcte as (
    SELECT	MID, CLASS,(Select ''+b.Class from #MD b where b.MID = a.MID for XML Path('')) as BKEY2 ---Case when Class = 'D' then 1 else 0 end
    FROM	#MD a
    ) Select *, Case when BKEY2 = 'D' then '100'
    				 when BKEY2 = 'M' then '010'
    				 when BKEY2 = 'V' then '001'
    				 when BKEY2 = 'DM' then '110'
    				 when BKEY2 = 'DMV' then '111'
    				 when BKEY2 = 'DV' then '101'
    				 when BKEY2 = 'MV' then '011'
    				 end as BKEY
    from wcte
    
    
    
    
    --IF OBJECT_ID('tempdb..#MD') IS NOT NULL
    --	DROP TABLE #MD


    Please visit my Blog for some easy and often used t-sql scripts
    My BizCard

    Wednesday, September 20, 2017 7:29 AM