Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    47

    Unanswered: Pivot Date Row to Column

    Hi,

    I am having some trouble with a pivot. I need SQL to take a list of dates and move them into columns so that I can have a grid of users.

    IE what I have is this.

    Code:
    ID   EMIS_ID   Date   Absence_Reason
    1     1         1/1/11   NULL
    2     1         1/2/11   Excused
    ....
    189  2          1/1/11   Unexcused
    190  2          1/2/11   NULL
    What I would like it to do is this.

    Code:
    EMIS_ID    1/1/11        1/2/11 ...
    1           NULL        Excused
    2           Unexcused   NULL
    Is this even possible? I have been trying to use a PIVOT statement but it seems like it needs an aggregate clause and I don't need to do any of that.

    Thanks.

    By the way, the reason that it is in this format is from my original post found here http://www.dbforums.com/microsoft-sq...ml#post6464129.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    SQL is not well suited to this. However, reporting tools eat this sort of thing up all day. Leave this formatting of data to the presentation layer. If this is not possible then you are in a world of dynamic SQL pain.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    Code:
     SELECT Column_name,
    SUM(CASE WHEN Column_name= 'A' THEN qty END) AS A,
    SUM(CASE WHEN Column_name = 'B' THEN qty END) AS B,
    SUM(CASE WHEN Column_name = 'C' THEN qty END) AS C,
    SUM(CASE WHEN Column_name = 'D' THEN qty END) AS D
    FROM Table_name
    GROUP BY Column_name
    Adjust the code may be it will work for you
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Note though the columns are dates. So that can't be done with hard coded column names.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    ooh k i didn't noticed that
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As a side note, SSAS would make short work of this problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    I think this is an example of the "world of dynamic SQL pain"

    Many would be agast at doing this within SQL, but sometimes you gota do what ya gota do . . .

    Wrote this a while back, which is why I have temporary tables rather than table variables.

    First query gathers the basic date for the report into a single table (#temp2), with each record containing a week-ending date.
    Second query gathers a list of distinct week-ending dates in that #temp2 table, posting the result to #temp.
    The next section uses a cursor to transform that week-ending date table into a string.
    And finally the last section uses a pivot SELECT to transform #temp2 into a week-based column report.

    Hope I didn't scare anyone.

    Code:
    ALTER procedure SAMPLE
    
    as
    
    /* TEST SECTION
    drop table #temp
    drop table #temp2
    --*/
    
    --Gather all of the data for the report
    select	tPDI.Discipline_Title
    		,tE.Alias
    		,convert(varchar,tPW.Week,111) Week
    		,Sum(tHR.Regular_Time+tHR.Overtime) Hours
    into	#temp2
    from	dbo.Hours_Record tHR with (NOLOCK)
    inner
    join	dbo.Employees tE with (NOLOCK) on
    			tE.id_employee=tHR.id_employee
    inner
    join	dbo.tblReportCriteriaCompaniesTMP_UID tRCC with (NOLOCK) on
    			tRCC.id_company=tE.id_company
    inner
    join	dbo.Pr_Dates tPD with (NOLOCK) on
    			tPD.id_date=tHR.hrs_date
    inner
    join	dbo.Pr_Weeks tPW with (NOLOCK) on
    			tPW.id_week=tPD.id_week
    inner
    join	dbo.tblReportCriteriaProjectsTMP_UID tRCP with (NOLOCK) on
    			tRCP.id_job=tHR.id_job
    inner
    join	dbo.Act_By_Job tABJ with (NOLOCK) on
    			tABJ.id_activity_by_job=tHR.id_activity_by_job
    			and tABJ.id_job=tHR.id_job
    inner
    join	dbo.Pr_Disciplines tPDI with (NOLOCK) on
    			tPDI.id_discipline=tABJ.id_discipline
    group
    by		tPDI.Discipline_Title
    		,tE.Alias
    		,convert(varchar,tPW.Week,111)
    order
    by		tPDI.Discipline_Title
    		,tE.Alias
    		,convert(varchar,tPW.Week,111)
    
    --Scan the data for distinct weeks
    select
    distinct
    		week
    into	#temp
    from	#temp2
    order
    by		week
    
    --Use cursor to build string of weeks from previous scan of data for distinct weeks
    declare @Week char(10)
    declare @WeeksString varchar(8000)
    
    set @WeeksString=''
    
    declare Weeks cursor for
    	(
    	select	Week
    	from	#temp
    	)
    open Weeks
    fetch next from Weeks INTO @Week
    while (@@fetch_status=0)
    begin
    	set @WeeksString=@WeeksString+'['+@Week+'],'
    	fetch next from Weeks INTO @Week
    end
    close Weeks
    deallocate Weeks
    
    if len(@WeeksString)=0
    	return
    
    set @WeeksString=left(@WeeksString,len(@WeeksString)-1)
    
    --Transform data into week-based column output
    declare @SQL varchar(8000)
    
    set @SQL='
    	select * from (select Discipline_Title,Alias,Week,Hours from #temp2) o
    	pivot (sum(Hours) for Week in('+@WeeksString+')) p
    	order
    	by		p.Discipline_Title,
    			p.Alias
    '
    
    --print @SQL
    exec(@SQL)
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  8. #8
    Join Date
    Jul 2009
    Posts
    47
    Thanks for all the ideas. I will have to give this all a try. I currently have a crosstab running in an MS Access frontend, it's just that it is really slow. That's why I was trying to get SQL to do it for me so that everything is handled in the backend.

    I will report back in a while.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •