SQL Pivot Help - Confusing Question :(

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
LVL 7
ManjuIT - Project ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
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
ManjuIT - Project ManagerAuthor Commented:
@Shaun - Any sample query to do this? I am going crazy since saturday. please help
0
Shaun KlineLead Software EngineerCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

ManjuIT - Project ManagerAuthor Commented:
@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
Shaun KlineLead Software EngineerCommented:
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
ManjuIT - Project ManagerAuthor Commented:
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
Shaun KlineLead Software EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.