Solved

SQL Pivot Help - Confusing Question :(

Posted on 2014-03-07
7
385 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 26

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 26

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 26

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 26

Accepted Solution

by:
Shaun Kline earned 500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Sum totals 2 21
Query Syntax 17 31
create an aggregate function 9 31
SQL - Update field defined as Text 6 15
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now