December 2, 2014 at 9:35 am
Hello everybody
I have the following query that will serve as a basis for SSRS report
SELECT TOP (1000) d.Project_Name, d.Status, d.Country, d.Region, p.Period, p.Quarter, p.Year, d.Brand, d.Store_Opens_Actual, d.DA, d.DPN, d.StoreNumber,
CONVERT(VARCHAR(10), CASE WHEN ISDATE(d .Store_Opens_Actual) = 1 THEN d .Store_Opens_Actual WHEN ISDATE(d .Store_Opens_Forecast) = 1 AND
ISDATE(d .Store_Opens_Actual) = 0 THEN d .Store_Opens_Forecast WHEN ISDATE(d .Store_Opens_Baseline) = 1 AND ISDATE(d .Store_Opens_Forecast) = 0 AND
ISDATE(d .Store_Opens_Actual) = 0 THEN d .Store_Opens_Baseline END, 101) AS Store_Open, CASE WHEN ISDATE(d .SAR_Accepted)
= 1 THEN 'YES' ELSE 'NO' END AS SAR_Accepted, CASE WHEN ISDATE(d .Construction_Started) = 1 THEN 'YES' ELSE 'NO' END AS Const_Started,
CASE WHEN ISDATE(d .Lease_Signed) = 1 THEN 'YES' ELSE 'NO' END AS Lease_Signed, CASE WHEN ISDATE(d .Closed_Date_Actual)
= 1 THEN 'YES' ELSE 'NO' END AS Closed, CONVERT(VARCHAR(10), CASE WHEN ISDATE(d .Closed_Date_Actual)
= 1 THEN d .Closed_Date_Actual WHEN ISDATE(d .Closed_Date_Forecast) = 1 AND ISDATE(d .Closed_Date_Actual) = 0 THEN d .Closed_Date_Forecast END, 101)
AS Store_Closed, CASE WHEN ISDATE(d .Store_Opens_Actual) = 1 THEN 'Y' ELSE 'N' END AS Open_Flag
FROM dbo.forecast_data AS d LEFT OUTER JOIN
dbo.Periods AS p ON (CASE WHEN ISDATE(d .Store_Opens_Actual) = 1 THEN d .Store_Opens_Actual WHEN ISDATE(d .Store_Opens_Forecast) = 1 AND
ISDATE(d .Store_Opens_Actual) = 0 THEN d .Store_Opens_Forecast WHEN ISDATE(d .Store_Opens_Baseline) = 1 AND ISDATE(d .Store_Opens_Forecast) = 0 AND
ISDATE(d .Store_Opens_Actual) = 0 THEN d .Store_Opens_Baseline END) >= p.PeriodStarts AND (CASE WHEN ISDATE(d .Store_Opens_Actual)
= 1 THEN d .Store_Opens_Actual WHEN ISDATE(d .Store_Opens_Forecast) = 1 AND ISDATE(d .Store_Opens_Actual)
= 0 THEN d .Store_Opens_Forecast WHEN ISDATE(d .Store_Opens_Baseline) = 1 AND ISDATE(d .Store_Opens_Forecast) = 0 AND ISDATE(d .Store_Opens_Actual)
= 0 THEN d .Store_Opens_Baseline END) <= p.PeriodEnds
WHERE (d.FileID = '411') AND (d.Status <> '12 - Dead Project') AND (d.Relocation <> 'Yes') AND (d.Stoplight_Status <> 'Red') AND
((SELECT CASE WHEN ISDATE(fd.Store_Opens_Actual) = 1 THEN fd.Store_Opens_Actual WHEN ISDATE(fd.Store_Opens_Forecast) = 1 AND
ISDATE(fd.Store_Opens_Actual) = 0 THEN fd.Store_Opens_Forecast WHEN ISDATE(fd.Store_Opens_Baseline) = 1 AND
ISDATE(fd.Store_Opens_Forecast) = 0 AND ISDATE(fd.Store_Opens_Actual) = 0 THEN fd.Store_Opens_Baseline END AS Expr1
FROM dbo.forecast_data AS fd
WHERE (recID = d.recID)) BETWEEN
(SELECT PeriodStarts
FROM dbo.Periods
WHERE (Period = 1) AND (Year = 2014)) AND
(SELECT PeriodEnds
FROM dbo.Periods AS Periods_1
WHERE (Period = 12) AND (Year = 2014)))
ORDER BY Store_Open
This returns a dataset, that I need to convert into a PIVOT table that should look like the attached spreadsheet.
Having trouble writing the PIVOT table query. I feel like I am missing something conceptually as I am not doing any summing or aggregation. I don't know if dynamics SQL is the solution here or which route to take. I don't know if there is such things as PIVOTING without aggregation. CROSS TAB came to my mind as well. Please, help.
thanks in advance,
Petr
December 2, 2014 at 9:45 am
Let's start with some formatting so we can read that query...
SELECT TOP (1000) d.Project_Name
,d.STATUS
,d.Country
,d.Region
,p.Period
,p.Quarter
,p.Year
,d.Brand
,d.Store_Opens_Actual
,d.DA
,d.DPN
,d.StoreNumber
,CONVERT(VARCHAR(10), CASE
WHEN ISDATE(d.Store_Opens_Actual) = 1
THEN d.Store_Opens_Actual
WHEN ISDATE(d.Store_Opens_Forecast) = 1
AND ISDATE(d.Store_Opens_Actual) = 0
THEN d.Store_Opens_Forecast
WHEN ISDATE(d.Store_Opens_Baseline) = 1
AND ISDATE(d.Store_Opens_Forecast) = 0
AND ISDATE(d.Store_Opens_Actual) = 0
THEN d.Store_Opens_Baseline
END, 101) AS Store_Open
,CASE
WHEN ISDATE(d.SAR_Accepted) = 1
THEN 'YES'
ELSE 'NO'
END AS SAR_Accepted
,CASE
WHEN ISDATE(d.Construction_Started) = 1
THEN 'YES'
ELSE 'NO'
END AS Const_Started
,CASE
WHEN ISDATE(d.Lease_Signed) = 1
THEN 'YES'
ELSE 'NO'
END AS Lease_Signed
,CASE
WHEN ISDATE(d.Closed_Date_Actual) = 1
THEN 'YES'
ELSE 'NO'
END AS Closed
,CONVERT(VARCHAR(10), CASE
WHEN ISDATE(d.Closed_Date_Actual) = 1
THEN d.Closed_Date_Actual
WHEN ISDATE(d.Closed_Date_Forecast) = 1
AND ISDATE(d.Closed_Date_Actual) = 0
THEN d.Closed_Date_Forecast
END, 101) AS Store_Closed
,CASE
WHEN ISDATE(d.Store_Opens_Actual) = 1
THEN 'Y'
ELSE 'N'
END AS Open_Flag
FROM dbo.forecast_data AS d
LEFT JOIN dbo.Periods AS p ON (
CASE
WHEN ISDATE(d.Store_Opens_Actual) = 1
THEN d.Store_Opens_Actual
WHEN ISDATE(d.Store_Opens_Forecast) = 1
AND ISDATE(d.Store_Opens_Actual) = 0
THEN d.Store_Opens_Forecast
WHEN ISDATE(d.Store_Opens_Baseline) = 1
AND ISDATE(d.Store_Opens_Forecast) = 0
AND ISDATE(d.Store_Opens_Actual) = 0
THEN d.Store_Opens_Baseline
END
) >= p.PeriodStarts
AND (
CASE
WHEN ISDATE(d.Store_Opens_Actual) = 1
THEN d.Store_Opens_Actual
WHEN ISDATE(d.Store_Opens_Forecast) = 1
AND ISDATE(d.Store_Opens_Actual) = 0
THEN d.Store_Opens_Forecast
WHEN ISDATE(d.Store_Opens_Baseline) = 1
AND ISDATE(d.Store_Opens_Forecast) = 0
AND ISDATE(d.Store_Opens_Actual) = 0
THEN d.Store_Opens_Baseline
END
) <= p.PeriodEnds
WHERE (d.FileID = '411')
AND (d.STATUS <> '12 - Dead Project')
AND (d.Relocation <> 'Yes')
AND (d.Stoplight_Status <> 'Red')
AND (
(
SELECT CASE
WHEN ISDATE(fd.Store_Opens_Actual) = 1
THEN fd.Store_Opens_Actual
WHEN ISDATE(fd.Store_Opens_Forecast) = 1
AND ISDATE(fd.Store_Opens_Actual) = 0
THEN fd.Store_Opens_Forecast
WHEN ISDATE(fd.Store_Opens_Baseline) = 1
AND ISDATE(fd.Store_Opens_Forecast) = 0
AND ISDATE(fd.Store_Opens_Actual) = 0
THEN fd.Store_Opens_Baseline
END AS Expr1
FROM dbo.forecast_data AS fd
WHERE (recID = d.recID)
) BETWEEN (
SELECT PeriodStarts
FROM dbo.Periods
WHERE (Period = 1)
AND (Year = 2014)
)
AND (
SELECT PeriodEnds
FROM dbo.Periods AS Periods_1
WHERE (Period = 12)
AND (Year = 2014)
)
)
ORDER BY Store_Open
Now let's examine your desired output...I have one word here. HUH???
Your desired output makes no sense at all based on the query. What is the logic here? Why is the first column empty for some rows but not others?
You should take a few minutes and read the first article referenced in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 2, 2014 at 9:49 am
Sorry about the formatting.
What I need is a listing of store openings by period.
I basically need a horizontal table (in SSRS at the end) that will list 12 periods and each period will list all stores (Project_name) opening in that period.
It feels like PIVOT without aggregation or Cross Tab, but I cannot seem to make it work.
I hope I am explaining this a little better now,
thanks for your time and help,
PEtr
December 2, 2014 at 9:57 am
vecerda (12/2/2014)
Sorry about the formatting.What I need is a listing of store openings by period.
I basically need a horizontal table (in SSRS at the end) that will list 12 periods and each period will list all stores (Project_name) opening in that period.
It feels like PIVOT without aggregation or Cross Tab, but I cannot seem to make it work.
I hope I am explaining this a little better now,
thanks for your time and help,
PEtr
I have a basic idea of what you want. I can't help with the code because I have nothing to work with.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 2, 2014 at 10:00 am
OK< let me get you some data. I am sorry about not reading the instructions first. I appreciate your time and help,
Petr
December 2, 2014 at 10:00 am
It's weird how this comes from time to time. A report with no relation between the columns in the same row.
Here's an example using basic sample data. It might get complex in your environment.
WITH SampleData AS(
SELECT 'Project A' Project_Name, 1 Period UNION ALL
SELECT 'Project B' Project_Name, 1 Period UNION ALL
SELECT 'Project C' Project_Name, 1 Period UNION ALL
SELECT 'Project D' Project_Name, 2 Period UNION ALL
SELECT 'Project E' Project_Name, 2 Period UNION ALL
SELECT 'Project F' Project_Name, 2 Period UNION ALL
SELECT 'Project G' Project_Name, 2 Period UNION ALL
SELECT 'Project H' Project_Name, 2 Period UNION ALL
SELECT 'Project I' Project_Name, 2 Period UNION ALL
SELECT 'Project J' Project_Name, 3 Period UNION ALL
SELECT 'Project K' Project_Name, 3 Period )
,ProjRowNum AS(
SELECT *,
ROW_NUMBER() OVER( PARTITION BY Period ORDER BY Project_Name) rn
FROM SampleData
)
SELECT
MAX( CASE WHEN Period = 1 THEN Project_Name ELSE '' END),
MAX( CASE WHEN Period = 2 THEN Project_Name ELSE '' END),
MAX( CASE WHEN Period = 3 THEN Project_Name ELSE '' END),
MAX( CASE WHEN Period = 4 THEN Project_Name ELSE '' END)
FROM ProjRowNum
GROUP BY rn
I haven't tried it, but you might want to have a "single row" with a "line-feed" separated list using the following method. http://www.sqlservercentral.com/articles/comma+separated+list/71700/
December 2, 2014 at 12:29 pm
Wow Luis. I knew a cross tab was the answer but I couldn't begin to put together some code from the original post. Well done sir!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 2, 2014 at 12:56 pm
Thank you Sean, this wasn't the first time I did it. Fortunately, this has only been a request in the forums.
This is an alternate way of doing it, depending on the front end capabilities.
WITH SampleData AS(
SELECT 'Project A' Project_Name, 1 Period UNION ALL
SELECT 'Project B' Project_Name, 1 Period UNION ALL
SELECT 'Project C' Project_Name, 1 Period UNION ALL
SELECT 'Project D' Project_Name, 2 Period UNION ALL
SELECT 'Project E' Project_Name, 2 Period UNION ALL
SELECT 'Project F' Project_Name, 2 Period UNION ALL
SELECT 'Project G' Project_Name, 2 Period UNION ALL
SELECT 'Project H' Project_Name, 2 Period UNION ALL
SELECT 'Project I' Project_Name, 2 Period UNION ALL
SELECT 'Project J' Project_Name, 3 Period UNION ALL
SELECT 'Project K' Project_Name, 3 Period
)
, ConcProjects AS(
SELECT Period,
STUFF( (SELECT CHAR(10) + CHAR(13) + Project_Name
FROM SampleData x
WHERE x.Period = s.Period
FOR XML PATH(''),type).value('.', 'varchar(8000)'), 1, 2, '') Projects
FROM SampleData s
GROUP BY Period
)
SELECT MAX( CASE WHEN Period = 1 THEN Projects ELSE '' END),
MAX( CASE WHEN Period = 2 THEN Projects ELSE '' END),
MAX( CASE WHEN Period = 3 THEN Projects ELSE '' END),
MAX( CASE WHEN Period = 4 THEN Projects ELSE '' END)
FROM ConcProjects
December 2, 2014 at 1:40 pm
Luis Cazares has been truly unbelievable. I have learnt from you so much, sir!
I truly appreciate all of your time and help.
In this case, you have taken an incomplete question and turned it into a full-fledged solution!
can't thank you enough,
Petr
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply