Solved

SQL Pivot Help - Confusing Question :(

Posted on 2014-03-07
7
382 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 25

Expert Comment

by:Shaun Kline
Comment Utility
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
Comment Utility
@Shaun - Any sample query to do this? I am going crazy since saturday. please help
0
 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 6

Author Comment

by:Manju
Comment Utility
@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 25

Expert Comment

by:Shaun Kline
Comment Utility
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
Comment Utility
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 25

Accepted Solution

by:
Shaun Kline earned 500 total points
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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

12 Experts available now in Live!

Get 1:1 Help Now