February 25, 2015 at 9:33 am
I am trying to take the results of a query and re-orient them into separate columns.
select distinct
W_SUMMARYDETAILS.FACILITY_ID,
W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year)
W_SUMMARYDETAILS.FACILITY_NAME,
W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year)
From W_SUMMARYDETAILS
full outer join W_DEF_SUMMARYDETAILS
on W_SUMMARYDETAILS.FACILITY_ID=W_DEF_SUMMARYDETAILS.FACILITY_ID and
W_SUMMARYDETAILS.REPORTING_YEAR=W_DEF_SUMMARYDETAILS.REPORTING_PERIOD
As of now the query puts all the years into a single column -- one for DEF_SUMMARY and another for SUMMARY.
I am looking to create 7 additional columns for all the individual years in the results instead of just two columns.
Any help much appreciated!
February 25, 2015 at 9:37 am
Hi and welcome to the forums. What you are describing is very common. One approach to this is to use crosstabs. There are two articles in my signature which explain this exact scenario. The first is a static pivot (when you know how many groups you have), the second is a dynamic version so it can handle an unknown number of groups. Take a look at those and post back if you need some help.
_______________________________________________________________
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/
February 25, 2015 at 10:06 am
I am tying to work on a small piece of the pie here with this code but it doesn't work as desired -- are there any glaring errors here? Thanks
select distinct
W_SUMMARYDETAILS.FACILITY_ID,
W_SUMMARYDETAILS.REPORTING_YEAR='2014' as [RY2014],
W_SUMMARYDETAILS.REPORTING_YEAR='2013' as [RY2013]
From W_SUMMARYDETAILS
February 25, 2015 at 10:09 am
storemannequin (2/25/2015)
I am tying to work on a small piece of the pie here with this code but it doesn't work as desired -- are there any glaring errors here? Thanksselect distinct
W_SUMMARYDETAILS.FACILITY_ID,
W_SUMMARYDETAILS.REPORTING_YEAR='2014' as [RY2014],
W_SUMMARYDETAILS.REPORTING_YEAR='2013' as [RY2013]
From W_SUMMARYDETAILS
Yes. You seem to be trying to use your column to filter only certain values. You would need to use a case expression here. However, in respect to your overall requirement this doesn't seem at all what you want.
_______________________________________________________________
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/
February 25, 2015 at 10:53 am
I'm just trying to get something to work so I can test it. Not sure what to put between the THEN and ELSE statement:
select
W_SUMMARYDETAILS.FACILITY_ID,
SUM (CASE WHEN W_SUMMARYDETAILS.REPORTING_YEAR='2014' THEN ELSE 0 END) as [RY2014]
FROM W_SUMMARYDETAILS
GROUP BY W_SUMMARYDETAILS.FACILITY_ID
February 25, 2015 at 11:53 am
The query produces this:
ID Year
1012011
2002012
1842011
1842013
1332014
1362011
1892012
1522013
1292011
1842013
1362011
but I want to change the queried results to look like this (with a count of Years):
Year
ID2011201220132014
1011
2001
18411
1331
1362
1891
1521
1291
1841
February 25, 2015 at 12:51 pm
I can help you but you will have to provide more details first:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
February 25, 2015 at 1:06 pm
I'm sorry, I'm trying to follow all the instructions but lost on how to create the test data for you. All that I'm trying to create are two columns, one with numerical IDs and another with years (2011 - 2014).
February 25, 2015 at 1:36 pm
storemannequin (2/25/2015)
I'm sorry, I'm trying to follow all the instructions but lost on how to create the test data for you. All that I'm trying to create are two columns, one with numerical IDs and another with years (2011 - 2014).
It isn't too complicated. You need to post create table statment(s) for the tables involved and insert statements with the values for those tables.
You know what the values are, you posted them and you posted what you want as output.
Here is how you would post your sample data.
if OBJECT_ID('tempdb..#MyTable') is not null
drop table #MyTable
create table #MyTable
(
ID int
, MyYear int
)
insert #MyTable
select 101, 2011 union all
select 200, 2012 union all
select 184, 2011 union all
select 184, 2013 union all
select 133, 2014 union all
select 136, 2011 union all
select 189, 2012 union all
select 152, 2013 union all
select 129, 2011 union all
select 184, 2013 union all
select 136, 2011
This makes it easy for us to consume and work on the problem.
Now using the techniques in the articles I suggested you would come up with something along these lines:
select ID
, SUM(case when MyYear = 2011 then 1 end) as Year2011
, SUM(case when MyYear = 2012 then 1 end) as Year2012
, SUM(case when MyYear = 2013 then 1 end) as Year2013
, SUM(case when MyYear = 2014 then 1 end) as Year2014
from #MyTable
group by ID
order by ID
That pretty closely matches what you stated you want as output but I think there may be a couple of errors in your desired output based on the values in your sample data.
_______________________________________________________________
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/
February 25, 2015 at 1:59 pm
Thank you so much, I've been able to amend that to my needs. Very much appreciated!
February 25, 2015 at 2:04 pm
storemannequin (2/25/2015)
Thank you so much, I've been able to amend that to my needs. Very much appreciated!
Excellent. Glad you were able to modify your code to incorporate this logic and thanks for letting me know. Come back anytime and the people around here will usually do their best to help.
_______________________________________________________________
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/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply