Solved

SQL Pivot Help - Confusing Question :(

Posted on 2014-03-07
7
394 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

628 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