none
PIVOT Table Help

    Question

  • So this is my result set...

    CodeID CODE_CAT CODE_VALUE DESCRIPTION

    17 SN NUL <NULL>

    13 NT IN In-Network

    9 CV FAM Family

    45 TQ 35 Week

    And here's how I want to present it horizontally...

    Sub-Net(SN) Network Indicator(IN) Coverage Level(CV) Time Period Qualifier(TQ)

    <NULL> In-Network Family Week

    How can I do this via a PIVOT?

    Thanks for your review and am praying for a reply.

    PSULionRP

    Friday, May 17, 2013 4:16 PM

Answers

  • E.g.

    WITH	CTE_STC_DETAIL_CODES	AS
    (
    	SELECT	
    			[Code_V_2].[CODE_CAT],
    		
    			[Code_V_2].[DESCRIPTION]
    	FROM	[dbo].[STC_Detail]
    	INNER JOIN	[STC_Header_V_2]				
    			ON	[STC_Header_V_2].[STCID]	=		[STC_Detail].[STCID]
    	INNER JOIN	[STC_Code]				
    			ON	[STC_Code].[STCDTLID]		=		[STC_Detail].[STCDTLID]
    	INNER JOIN	[Code_V_2]				
    			ON	[Code_V_2].[CodeID]				=		[STC_Code].[CodeID]
    	WHERE	[STC_Header_V_2].[STC]			=		'33 '
    )
    
    SELECT	[STCDTLID],
    		[SN]									AS	'Sub Net',
    		[NT]									AS	'Network Indicator',
    		[CV]									AS	'Coverage Level',
    		[TQ]									AS	'Time Period Qualifier',
    		[AI]									AS	'Authorization Indicator',
    		[CS]									AS	'Cost Share Type',
    		[IC]									AS	'Insurance Certificate Code',
    		[QQ]									AS	'Quantity Qualifier Code'
    FROM	CTE_STC_DETAIL_CODES
    PIVOT
    (
    	MAX([DESCRIPTION])
    	FOR	CODE_CAT			IN
    	(
    	[SN],
    	[NT],
    	[CV],
    	[TQ],
    	[AI],
    	[CS],
    	[IC],
    	[QQ]
    	)) AS Pvt


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by PSULionRP Friday, May 17, 2013 7:12 PM
    Friday, May 17, 2013 6:48 PM
    Moderator

All replies

  • Try

    select max(case when Code_Cat = 'SN' then Description END) as [Sub-Net(SN)],
    
    max(case when Code_Cat = 'IN' then Description END) as [Network Indicator(IN)],
    
    etc.
    
    from MyResultSet
    This is case based pivot.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, May 17, 2013 5:59 PM
    Moderator
  • select max(case when Code_Cat = 'SN' then Description END) as [Sub-Net(SN)],
           max(case when Code_Cat = 'NT' then Description END) as [Network Indicator(IN)],
           max(case when Code_Cat = 'CV' then Description END) as [Coverage Level(CV)],
           max(case when Code_Cat = 'TQ' then Description END) as [ime Period Qualifier(TQ)]
    from MyResultSet


    Belo Horizonte, MG - Brasil

    Friday, May 17, 2013 6:27 PM
  • That's sooooo close....My key is STCDTLID

    USE	[Pulse]
    GO
    WITH	CTE_STC_DETAIL_CODES	AS
    (
    	SELECT	[STC_Detail].[STCDTLID],
    			[STC_Code].[CodeID],
    			[Code_V_2].[CODE_CAT],
    			[Code_V_2].[CODE_VALUE],
    			[Code_V_2].[DESCRIPTION]
    	FROM	[dbo].[STC_Detail]
    	INNER JOIN	[STC_Header_V_2]				
    			ON	[STC_Header_V_2].[STCID]	=		[STC_Detail].[STCID]
    	INNER JOIN	[STC_Code]				
    			ON	[STC_Code].[STCDTLID]		=		[STC_Detail].[STCDTLID]
    	INNER JOIN	[Code_V_2]				
    			ON	[Code_V_2].[CodeID]				=		[STC_Code].[CodeID]
    	WHERE	[STC_Header_V_2].[STC]			=		'33 '
    )
    
    SELECT	[STCDTLID],
    		[SN]									AS	'Sub Net',
    		[NT]									AS	'Network Indicator',
    		[CV]									AS	'Coverage Level',
    		[TQ]									AS	'Time Period Qualifier',
    		[AI]									AS	'Authorization Indicator',
    		[CS]									AS	'Cost Share Type',
    		[IC]									AS	'Insurance Certificate Code',
    		[QQ]									AS	'Quantity Qualifier Code'
    FROM	CTE_STC_DETAIL_CODES
    PIVOT
    (
    	MAX([DESCRIPTION])
    	FOR	CODE_CAT			IN
    	(
    	[SN],
    	[NT],
    	[CV],
    	[TQ],
    	[AI],
    	[CS],
    	[IC],
    	[QQ]
    	))											AS	C2
    GROUP BY	[STCDTLID],
    			[SN],
    			[NT],
    			[CV],
    			[TQ],
    			[AI],
    			[CS],
    			[IC],
    			[QQ]
    
    		
    

    The only problem is that I'm getting 8 rows in my result set....all with STCDTLID = 1...I just want one row...

    Friday, May 17, 2013 6:38 PM
  • Did you try my suggestion? In your case you need to only select Description and Code_Cat columns to be your source table instead of all of them.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, May 17, 2013 6:45 PM
    Moderator
  • E.g.

    WITH	CTE_STC_DETAIL_CODES	AS
    (
    	SELECT	
    			[Code_V_2].[CODE_CAT],
    		
    			[Code_V_2].[DESCRIPTION]
    	FROM	[dbo].[STC_Detail]
    	INNER JOIN	[STC_Header_V_2]				
    			ON	[STC_Header_V_2].[STCID]	=		[STC_Detail].[STCID]
    	INNER JOIN	[STC_Code]				
    			ON	[STC_Code].[STCDTLID]		=		[STC_Detail].[STCDTLID]
    	INNER JOIN	[Code_V_2]				
    			ON	[Code_V_2].[CodeID]				=		[STC_Code].[CodeID]
    	WHERE	[STC_Header_V_2].[STC]			=		'33 '
    )
    
    SELECT	[STCDTLID],
    		[SN]									AS	'Sub Net',
    		[NT]									AS	'Network Indicator',
    		[CV]									AS	'Coverage Level',
    		[TQ]									AS	'Time Period Qualifier',
    		[AI]									AS	'Authorization Indicator',
    		[CS]									AS	'Cost Share Type',
    		[IC]									AS	'Insurance Certificate Code',
    		[QQ]									AS	'Quantity Qualifier Code'
    FROM	CTE_STC_DETAIL_CODES
    PIVOT
    (
    	MAX([DESCRIPTION])
    	FOR	CODE_CAT			IN
    	(
    	[SN],
    	[NT],
    	[CV],
    	[TQ],
    	[AI],
    	[CS],
    	[IC],
    	[QQ]
    	)) AS Pvt


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by PSULionRP Friday, May 17, 2013 7:12 PM
    Friday, May 17, 2013 6:48 PM
    Moderator
  • PERFECT!

    THAT WORKED!

    The only thing I had to do was re-add the [STCDTLID] back into the CTE!

    Y'All are the BEST!!!!!!!!!!

    Friday, May 17, 2013 7:11 PM
  • Now if I only understood what the hell I just did...

    Care to explain???

    Friday, May 17, 2013 7:11 PM
  • When we use PIVOT operation, we need to exclude columns making each row unique if these columns are not the ones we're using in aggregate function in the PIVOT part. Otherwise in the result we will get a row for each unique column combinations since all rows which are not included into PIVOT part are used to aggregate upon.

    See my second solution (case based pivot) to understand it better.

    Also, this blog post

    Understanding SQL Server 2000 Pivot with Aggregates

    may be helpful in understanding how PIVOT works.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, May 17, 2013 7:35 PM
    Moderator