locked
complex sql RRS feed

  • Question

  • Hi experts

    i need help in writing sql to retrive data in format like presntly for 101 id it is showing 3 rows because of three different dates in d3 column.....

    i want to have single row per id with all three different dates as different column.

    If this is possible please help

    present data look like :-

    with
    tab1 as
    (
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-07-26' as dt3, '2010-07-29' as dt4
    union all
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-07-29' as dt3, '2010-08-05' as dt4
    union all
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-12-07' as dt3, '2010-12-14' as dt4
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-03-11' as dt3, '2011-03-11' as dt4
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-03-31' as dt3, '2011-04-02' as dt4
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-04-15' as dt3, '' as dt4
    )select * from tab1

     

    result required

    with
    req_result as
    (
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-07-26' as dt3, '2010-07-29' as dt3_1, '2010-12-07' as dt3_2,'2010-07-29' as dt4,'2010-08-05' as dt4_1,'2010-12-14' as dt4_2
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-03-11' as dt3, '2011-03-31' as dt3_1,'2011-04-15' as dt3_2, '2011-03-11' as dt4, '2011-04-02' as dt4_1,'' as dt4_2
    )select * from req_result

    Thursday, May 5, 2011 8:35 AM

Answers

  • I am asuming here can be n number of dates in those four columns, and we may have n number of dt4 , dt4_1 , dt4_2 , dt4_3 , dt4_4  , dt4_n etc.. then give this a  try..

    /*
    
    If running multiple times this might help
    
    DROP TABLE #TEMP
    DROP TABLE #TEMP2
    
    */
    
    DROP TABLE #TEMP
    DROP TABLE #TEMP2
    
    DECLARE @Headers AS VARCHAR(MAX)
    
    ;with
    tab1 as
    (
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-07-26' as dt3, '2010-07-29' as dt4
    union all
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-07-29' as dt3, '2010-08-05' as dt4
    union all
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-12-07' as dt3, '2010-12-14' as dt4
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-03-11' as dt3, '2011-03-11' as dt4
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-03-31' as dt3, '2011-04-02' as dt4
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-04-15' as dt3, '' as dt4
    )select * INTO #temp from tab1
    
    
    
    ;WITH
    Base
    AS
    (
    	SELECT
    			'DT1' [My_DT1],
    			'DT2' [My_DT2],
    			'DT3' [My_DT3],
    			'DT4' [My_DT4],
    			*
    	FROM
    			#temp 		
    )
    ,
    Linear
    AS
    (
    SELECT
    		*
    FROM
    		(		
    			SELECT
    					Id,
    					Dt1,
    					My_DT1 AS [Indicator]
    			FROM
    					Base
    			UNION 
    			SELECT 
    					Id,
    					Dt2,
    					My_DT2 
    			FROM
    					Base 	
    			UNION 
    			SELECT 
    					Id,
    					Dt3,
    					My_DT3 
    			FROM 
    					Base 
    			UNION 
    			SELECT 
    					Id,
    					Dt4,
    					My_DT4 
    			FROM 
    					Base 
    		)A
    )		
    SELECT
    	@Headers = 
    			(
    				SELECT
    						DISTINCT 
    						 '[' + Aa.ColumnHeader + '],'
    				FROM
    						(	
    							SELECT
    									Id, 
    									Dates,
    									CASE WHEN Seq != 0 THEN A.Indicator + '_' + CAST(Seq AS VARCHAR(1))
    									ELSE Indicator 
    									END AS [ColumnHeader]
    							FROM
    									(		
    										SELECT
    												L.Id,
    												L.Dt1 AS [Dates],
    												L.Indicator,
    												ROW_NUMBER()
    												OVER 
    												(
    													PARTITION BY Id, Indicator
    													ORDER BY Id, Dt1 desc
    												) - 1 AS [Seq]
    										FROM
    												Linear L
    									)A
    						)AA
    				FOR XML PATH('')
    			)	
    
    SET @Headers = SUBSTRING(@Headers,1,(len(@Headers) -1))
    
    	
    ;WITH
    Base
    AS
    (
    	SELECT
    			'DT1' [My_DT1],
    			'DT2' [My_DT2],
    			'DT3' [My_DT3],
    			'DT4' [My_DT4],
    			*
    	FROM
    			#temp 		
    )
    ,
    Linear
    AS
    (
    SELECT
    		*
    FROM
    		(		
    			SELECT
    					Id,
    					Dt1,
    					My_DT1 AS [Indicator]
    			FROM
    					Base
    			UNION 
    			SELECT 
    					Id,
    					Dt2,
    					My_DT2 
    			FROM
    					Base 	
    			UNION 
    			SELECT 
    					Id,
    					Dt3,
    					My_DT3 
    			FROM 
    					Base 
    			UNION 
    			SELECT 
    					Id,
    					Dt4,
    					My_DT4 
    			FROM 
    					Base 
    		)A
    )		
    
    	SELECT
    			*		 
    	INTO	#temp2
    	FROM
    			(		
    				SELECT
    						Id, 
    						Dates,
    						CASE WHEN Seq != 0 THEN A.Indicator + '_' + CAST(Seq AS VARCHAR(1))
    						ELSE Indicator 
    						END AS [ColumnHeader]
    				FROM
    						(		
    							SELECT
    									L.Id,
    									L.Dt1 AS [Dates],
    									L.Indicator,
    									ROW_NUMBER()
    									OVER 
    									(
    										PARTITION BY Id, Indicator
    										ORDER BY Id, Dt1 desc
    									) - 1 AS [Seq]
    							FROM
    									Linear L
    						)A
    			)AA
    
    DECLARE @query VARCHAR(MAX)
    SET 
    	@query = 
    			'
    		SELECT
    				*
    		FROM
    				#temp2	A
    		PIVOT 	
    				(
    					MIN(A.[Dates])
    					FOR
    					A.ColumnHeader
    					IN('+ @Headers +')
    				)AS PT'
    
    exec(@query)
    
    
    
    • Proposed as answer by bobragland Thursday, May 5, 2011 6:43 PM
    • Marked as answer by Kalman Toth Thursday, May 12, 2011 11:08 AM
    Thursday, May 5, 2011 6:42 PM

All replies

  • You need to use dynami pivot here. chk the links below

    Pivot :
    http://msdn.microsoft.com/en-us/library/ms177410.aspx

    dynamic pivot
    http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
    Thursday, May 5, 2011 8:43 AM
  • Try this:
    with
    tab1 as
    (
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-07-26' as dt3, '2010-07-29' as dt4
    union all
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-07-29' as dt3, '2010-08-05' as dt4
    union all
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-12-07' as dt3, '2010-12-14' as dt4
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-03-11' as dt3, '2011-03-11' as dt4
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-03-31' as dt3, '2011-04-02' as dt4
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-04-15' as dt3, '' as dt4
    ), x as (
    
      select RN = row_number() over (partition by id order by dt3), * from tab1 
    )
    select id, max(dt1) dt1, max(dt2) dt2, max(case when rn = 1 then dt3 else '' end) dt3,
     max(case when rn = 2 then dt3 else '' end) dt3_1,
     max(case when rn = 3 then dt3 else '' end) dt3_2,
     max(case when rn = 1 then dt4 else '' end) dt4,
     max(case when rn = 2 then dt4 else '' end) dt4_1,
     max(case when rn = 3 then dt4 else '' end) dt4_2
    from x group by id
    
    

    The simpler the solution the stronger it is
    If this post answers you, please mark it as answer..
    If this post is useful, please vote it as useful..
    Thursday, May 5, 2011 9:08 AM
  • Hi

    thanks for the reply... i have tried creating pivot table but getting an error as "Invalid column name 'ID'."

    could you please check and let me know what fundamental mistake i have done.

     with
    tab1 as
    (
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-07-26' as dt3, '2010-07-29' as dt4
    union all
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-07-29' as dt3, '2010-08-05' as dt4
    union all
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-12-07' as dt3, '2010-12-14' as dt4
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-03-11' as dt3, '2011-03-11' as dt4
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-03-31' as dt3, '2011-04-02' as dt4
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-04-15' as dt3, '' as dt4
    )
    SELECT ID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
    FROM
    (SELECT ID, dt3 FROM tab1) p
    PIVOT
    (
    COUNT (ID)
    FOR dt3 IN
    ( [250], [251], [256], [257], [260] )
    ) AS pvt

    regards

     

    Thursday, May 5, 2011 9:13 AM
  • Hi,

    it has to be something like this

    select * from (select id,dt3 from tab1)p
    pivot
    (max(dt3) for dt3 in([2010-07-26],[2010-07-29],[2010-12-07]))pvt
    --where id=101


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Thursday, May 5, 2011 9:19 AM
  • The IN clause in dt3 IN ()

    should have the actual data (in this case dates like  '2010-07-26').

    Where did you get the [250], [251] etc from?


    The simpler the solution the stronger it is
    If this post answers you, please mark it as answer..
    If this post is useful, please vote it as useful..
    Thursday, May 5, 2011 9:22 AM
  • Hi,

    Here is the way to do it dynamically


    declare @cols varchar(max)
    set @cols=stuff((select ','+QUOTENAME(dt3) as [text()] from #temp for xml path('')),1,1,'')

    declare @sql nvarchar(max)
    select @sql='select * from (select id,dt3 from #temp)p
    pivot
    (max(dt3) for dt3 in('+@cols+'))pvt'
    --where id=101

    exec (@sql)

    Note: change your cte to temp table


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Thursday, May 5, 2011 9:29 AM
  • Hi

    thanks for the reply..... it is impossible to put 60k data values like this..... is there any other way from which it can take dynamically data values in "IN" parameter

    regards

    Thursday, May 5, 2011 9:30 AM
  • Hi,

    Have a look to my previous post and also the below link

    http://jahaines.blogspot.com/2009/08/pivoting-data-using-sql-2005-part-2.html


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Thursday, May 5, 2011 9:34 AM
  • Hi

    thanks for your help... the problem with your previous solution is that it is creating 7 column in total but i require only 4 columns to be created

    sorry being pest .... i have never done pivot so it is getting difficult ........

    regards

    Thursday, May 5, 2011 9:49 AM
  • Hi,

    The number of output column depends on the values present in your columns dt3.

    What exactly output you want?

     


    Thanks and regards, Rishabh , Microsoft Community Contributor

    Thursday, May 5, 2011 10:01 AM
  • Hi

    i want data to be shown like this:-

    with
    req_result as
    (
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-07-26' as dt3, '2010-07-29' as dt3_1, '2010-12-07' as dt3_2,'2010-07-29' as dt4,'2010-08-05' as dt4_1,'2010-12-14' as dt4_2
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-03-11' as dt3, '2011-03-31' as dt3_1,'2011-04-15' as dt3_2, '2011-03-11' as dt4, '2011-04-02' as dt4_1,'' as dt4_2
    )select * from req_result

    let me know if you need more info

    regards 

    Thursday, May 5, 2011 10:43 AM
  • Hi,

    try this

    ;with cte as
    (
    select *,ROW_NUMBER()over(partition by id order by id) as r from #temp
    )

    select * from(select id,dt1,dt2,[1] as dt3,[2] as dt3_1,[3] as dt3_2 from (select id,dt1,dt2,dt3,R from cte)p
    pivot
    (max(dt3) for r in([1],[2],[3]))pvt)x
    cross apply
    (select id,dt1,dt2,[1] as dt4,[2] as dt4_1,[3] as dt4_2 from (select id,dt1,dt2,dt4,R from cte)p
    pivot
    (max(dt4) for r in([1],[2],[3]))pvt)x1
    where x.id=x1.id

     

     


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Thursday, May 5, 2011 11:01 AM
  • I am asuming here can be n number of dates in those four columns, and we may have n number of dt4 , dt4_1 , dt4_2 , dt4_3 , dt4_4  , dt4_n etc.. then give this a  try..

    /*
    
    If running multiple times this might help
    
    DROP TABLE #TEMP
    DROP TABLE #TEMP2
    
    */
    
    DROP TABLE #TEMP
    DROP TABLE #TEMP2
    
    DECLARE @Headers AS VARCHAR(MAX)
    
    ;with
    tab1 as
    (
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-07-26' as dt3, '2010-07-29' as dt4
    union all
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-07-29' as dt3, '2010-08-05' as dt4
    union all
    select '101' as id, '2010-06-04' as Dt1, '2010-06-09' as Dt2, '2010-12-07' as dt3, '2010-12-14' as dt4
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-03-11' as dt3, '2011-03-11' as dt4
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-03-31' as dt3, '2011-04-02' as dt4
    union all
    select '102' as id, '2010-10-13' as Dt1, '2010-10-13' as Dt2, '2011-04-15' as dt3, '' as dt4
    )select * INTO #temp from tab1
    
    
    
    ;WITH
    Base
    AS
    (
    	SELECT
    			'DT1' [My_DT1],
    			'DT2' [My_DT2],
    			'DT3' [My_DT3],
    			'DT4' [My_DT4],
    			*
    	FROM
    			#temp 		
    )
    ,
    Linear
    AS
    (
    SELECT
    		*
    FROM
    		(		
    			SELECT
    					Id,
    					Dt1,
    					My_DT1 AS [Indicator]
    			FROM
    					Base
    			UNION 
    			SELECT 
    					Id,
    					Dt2,
    					My_DT2 
    			FROM
    					Base 	
    			UNION 
    			SELECT 
    					Id,
    					Dt3,
    					My_DT3 
    			FROM 
    					Base 
    			UNION 
    			SELECT 
    					Id,
    					Dt4,
    					My_DT4 
    			FROM 
    					Base 
    		)A
    )		
    SELECT
    	@Headers = 
    			(
    				SELECT
    						DISTINCT 
    						 '[' + Aa.ColumnHeader + '],'
    				FROM
    						(	
    							SELECT
    									Id, 
    									Dates,
    									CASE WHEN Seq != 0 THEN A.Indicator + '_' + CAST(Seq AS VARCHAR(1))
    									ELSE Indicator 
    									END AS [ColumnHeader]
    							FROM
    									(		
    										SELECT
    												L.Id,
    												L.Dt1 AS [Dates],
    												L.Indicator,
    												ROW_NUMBER()
    												OVER 
    												(
    													PARTITION BY Id, Indicator
    													ORDER BY Id, Dt1 desc
    												) - 1 AS [Seq]
    										FROM
    												Linear L
    									)A
    						)AA
    				FOR XML PATH('')
    			)	
    
    SET @Headers = SUBSTRING(@Headers,1,(len(@Headers) -1))
    
    	
    ;WITH
    Base
    AS
    (
    	SELECT
    			'DT1' [My_DT1],
    			'DT2' [My_DT2],
    			'DT3' [My_DT3],
    			'DT4' [My_DT4],
    			*
    	FROM
    			#temp 		
    )
    ,
    Linear
    AS
    (
    SELECT
    		*
    FROM
    		(		
    			SELECT
    					Id,
    					Dt1,
    					My_DT1 AS [Indicator]
    			FROM
    					Base
    			UNION 
    			SELECT 
    					Id,
    					Dt2,
    					My_DT2 
    			FROM
    					Base 	
    			UNION 
    			SELECT 
    					Id,
    					Dt3,
    					My_DT3 
    			FROM 
    					Base 
    			UNION 
    			SELECT 
    					Id,
    					Dt4,
    					My_DT4 
    			FROM 
    					Base 
    		)A
    )		
    
    	SELECT
    			*		 
    	INTO	#temp2
    	FROM
    			(		
    				SELECT
    						Id, 
    						Dates,
    						CASE WHEN Seq != 0 THEN A.Indicator + '_' + CAST(Seq AS VARCHAR(1))
    						ELSE Indicator 
    						END AS [ColumnHeader]
    				FROM
    						(		
    							SELECT
    									L.Id,
    									L.Dt1 AS [Dates],
    									L.Indicator,
    									ROW_NUMBER()
    									OVER 
    									(
    										PARTITION BY Id, Indicator
    										ORDER BY Id, Dt1 desc
    									) - 1 AS [Seq]
    							FROM
    									Linear L
    						)A
    			)AA
    
    DECLARE @query VARCHAR(MAX)
    SET 
    	@query = 
    			'
    		SELECT
    				*
    		FROM
    				#temp2	A
    		PIVOT 	
    				(
    					MIN(A.[Dates])
    					FOR
    					A.ColumnHeader
    					IN('+ @Headers +')
    				)AS PT'
    
    exec(@query)
    
    
    
    • Proposed as answer by bobragland Thursday, May 5, 2011 6:43 PM
    • Marked as answer by Kalman Toth Thursday, May 12, 2011 11:08 AM
    Thursday, May 5, 2011 6:42 PM