Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Pivot Help - Confusing Question :(

Posted on 2014-03-07
7
Medium Priority
?
399 Views
Last Modified: 2014-03-19
Team -

Have a confusing question, so apologies before i start the question.

Here's a sample data I have Test-EE.xlsx

This is just a sample. The over all data has over 250+ columns, however I am concerned only on the above sample columns which are date columns.

Question is, Depending upon the lifecycle column(last column), i need to display the table. So if experts can help me get one, i will build the others.

Wht i need to present is this..,

1. I need to show for current year, last year & next year (36 columns)
2. Respective Date columns as Rows
3. Sample here.Sample Picture
Please help
0
Comment
Question by:Manju
  • 4
  • 3
7 Comments
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 39914052
The way in which you want to display the data does not fall into the definition of SQL Server's Pivot command. As you want to transform multiple columns from a single row of data into multiple rows, you may be better suited to use a union of multiple queries, were each query represents the category of each row in your output.
0
 
LVL 6

Author Comment

by:Manju
ID: 39920250
@Shaun - Any sample query to do this? I am going crazy since saturday. please help
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 39922519
The basic query would be:

SELECT ProjectNumber, 'PE (Analysis & Plan)' AS Phase, PEStart AS PhaseStartDate, PEStart AS PhaseEndDate
FROM <your table>

For phases that have both a start and end date, substitute accordingly. With a query built for each phase/sub phase, you can pull them all together with UNIONs.

Once you have the data in this row per phase manner, displaying the data will depend on the tool you are using.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Author Comment

by:Manju
ID: 39924154
@Shaun - Thanks for your guidance. I am able to pull up everything in union query. Actually my main concern there was on the manual fields.

ex:

from July to June (36 columns). Let me share a query which i have tried & working for a different view.

;with years as(
  SELECT 2012 as y union all
  SELECT 2013 as y union all
  SELECT 2014 as y union all
  select 2015 as y),
months as (
  SELECT 1 as m UNION all
  SELECT m+1 FROM months WHERE m < 12),
alldates as (
  SELECT DATEADD(year, y-1900, DATEADD(month, m-1, DATEADD(day, 0, 0))) AS dmy
  FROM months cross join years),
firstdays as (
  select [TBL_Programs#Request No], [TBL_Project & WBS#Request No], [Project Name], 
  DATEADD(month, DATEDIFF(month, 0, [TBL_Project & WBS#Planned Start Period]), 0) [Start Date], 
  DATEADD(month, DATEDIFF(month, 0, [TBL_Project & WBS#Planned Finish Period]), 0) [Finish Date], 
  DATEADD(month, DATEDIFF(month, 0, [PE Gate Date]), 0) [PE Gate Date], 
  DATEADD(month, DATEDIFF(month, 0, [PC Gate Date]), 0) [PC Gate Date], 
  DATEADD(month, DATEDIFF(month, 0, [LPA Gate Date]), 0) [LPA Gate Date], 
  DATEADD(month, DATEDIFF(month, 0, [LA Gate Date]), 0) [LA Gate Date], 
  DATEADD(month, DATEDIFF(month, 0, [LR Gate Date]), 0) [LR Gate Date]
  from Program_Status)
select fd.[TBL_Programs#Request No], 
fd.[TBL_Project & WBS#Request No], 
fd.[Project Name],
ad.dmy,
CASE 
 WHEN fd.[Start Date] <= ad.dmy AND fd.[PE Gate Date] > ad.dmy THEN 'DI'
 WHEN fd.[PE Gate Date] <= ad.dmy AND fd.[PC Gate Date] > ad.dmy THEN 'DE'
 WHEN fd.[PC Gate Date] <= ad.dmy AND fd.[LPA Gate Date] > ad.dmy THEN 'QU'
 WHEN fd.[LPA Gate Date] <= ad.dmy AND fd.[LA Gate Date] > ad.dmy THEN 'RE'
 WHEN fd.[LA Gate Date] <= ad.dmy AND fd.[LR Gate Date] > ad.dmy THEN 'LA'
 WHEN fd.[LR Gate Date]<= ad.dmy AND fd.[Finish Date] > ad.dmy THEN 'FI'

 ELSE '' 
END AS value
INTO #temp
from firstdays fd 
CROSS JOIN alldates ad 
ORDER BY 1,2,3,4,5;

Declare @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(varchar(10), c.dmy, 121)) 
            FROM #temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [TBL_Programs#Request No], [TBL_Project & WBS#Request No], [Project Name], ' + @cols + ' from 
            (
                select [TBL_Programs#Request No], [TBL_Project & WBS#Request No], [Project Name]
                    , dmy
                    , value
                from #temp 
           ) x
            pivot 
            (
                 max(value)
                for dmy in (' + @cols + ')
            ) p where [TBL_Programs#Request No] = @ProgramNumber'


execute sp_executesql @query, N'@ProgramNumber nvarchar(max)', @ProgramNumber = @ProgramNumber

DROP TABLE #temp

Open in new window


This works fine for projects with 36 columns (actually more, but am using SSRS to show only the fields i wanted).

This show 1 project per row, however all the related phases are in the row itself.

now i need to show 1 project into multiple phases as different rows. How can i achieve this? Union query did give the raw data per my need. however how can i put this into action? any assistance please?
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 39926371
Attempting to pivot on a date field will be extremely difficult, especially if you wish to use the query repeatedly for different datasets. The reason is that the PIVOT command requires that you use named values. By named values, I mean you have to pre-define the pivoted columns. If you had consistent column names, such as your phases, this would be easier. However, your dates will change for each project, meaning that the named values in your PIVOT would also need to change with each project to account for the different date range. This would require dynamic SQL which, though doable, I do not highly recommend due to the complexity of the SQL you will need to generate. If you are not married to the look and feel of the Gantt chart you are trying to display, look at the Range Bar Chart in SSRS. This chart can be used to create Gantt-looking chart.

There are numerous step-by-step examples on the web that describe how to create this chart in SSRS. Here is one example:
http://pnarayanaswamy.blogspot.com/2010/09/range-bar-chart-gantt-chart-using-ssrs.html
0
 
LVL 6

Author Comment

by:Manju
ID: 39926480
Well, chart is not an option as it has to be a table which calls all the fiscal year/months too. Nonetheless, i used your suggestion on union and im working on it.

Mostly, I'll be able to close this one by tomorrow. I shall update the code for my purpose as well & will grant you the points.

Thanks for your help Shaun.

Regards,
Manju
0
 
LVL 27

Accepted Solution

by:
Shaun Kline earned 2000 total points
ID: 39934155
Giving this a little more thought, you may be able to do this by creating a temporary table that contains a column for the phase description and then 36 columns, one for each month that you need to display. The data in the 36 month columns that you would use to indicate what gets displayed in your table. Your report could then interpret the 36 columns into your diamond, the cell color or nothing.

To handle your fiscal year labels, all you would need is the start date for the table that you are displaying, and perform calculations for each of the three fiscal year periods.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question