March 27, 2013 at 1:52 pm
How do i line up the weeks ('Weeks-2013' and 'Weeks-2012') in T-SQL ?
Below is sample data to test with:
SELECT DISTINCT 'International' Campus,28 'Weeks-2013',0 'Weeks-2012',2 'Student-2013',0 'Student-2012',0 StudentPrevYrTotal
UNION
SELECT DISTINCT 'International' Campus,32 'Weeks-2013',0 'Weeks-2012',1 'Student-2013',0 'Student-2012',0 StudentPrevYrTotal
UNION
SELECT DISTINCT 'International' Campus,0 'Weeks-2013',32 'Weeks-2012',0 'Student-2013',1 'Student-2012',1 StudentPrevYrTotal
UNION
SELECT DISTINCT 'International' Campus,29 'Weeks-2013',0 'Weeks-2012',6 'Student-2013',0 'Student-2012',0 StudentPrevYrTotal
UNION
SELECT DISTINCT 'International' Campus,27 'Weeks-2013',0 'Weeks-2012',5 'Student-2013',0 'Student-2012',0 StudentPrevYrTotal
UNION
SELECT DISTINCT 'International' Campus,24 'Weeks-2013',0 'Weeks-2012',4 'Student-2013',0 'Student-2012',0 StudentPrevYrTotal
UNION
SELECT DISTINCT 'International' Campus,0 'Weeks-2013',24 'Weeks-2012',0 'Student-2013',3 'Student-2012',4 StudentPrevYrTotal
ORDER BY
Campus,'Weeks-2013','Weeks-2012'
The idea is to achieve following:
Campus / 'Weeks-2013'/ 'Weeks-2012' / 'Student-2013' / 'Student-2012'
International/ 24 / 24 / 4 / 3
International/ 27 / 0 / 5 / 0
International/ 28 / 0 / 2 / 0
International/ 29 / 0 / 6 / 0
International/ 32 / 32 / 1 / 1
24,27,28,29,32 are the week numbers.
March 27, 2013 at 1:55 pm
What do you mean by "line up the weeks"???
_______________________________________________________________
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/
March 27, 2013 at 1:57 pm
...ORDER BY ?
Lowell
March 27, 2013 at 2:41 pm
Ho Sean & Lowell, updated my post - hope you will now understand what I am asking ?
Thanks
March 27, 2013 at 3:22 pm
kevin_nikolai (3/27/2013)
Ho Sean & Lowell, updated my post - hope you will now understand what I am asking ?Thanks
Aside from turning 3 rows into 2 rows and some values moved around it doesn't help much. What are the business rules you are trying to accomplish?
_______________________________________________________________
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/
March 28, 2013 at 3:43 am
I think this is what you want:
;
with CTE as (
SELECT 'International' Campus,28 'Weeks-2013',0 'Weeks-2012',2 'Student-2013',0 'Student-2012',0 StudentPrevYrTotal
UNION
SELECT 'International' Campus,32 'Weeks-2013',0 'Weeks-2012',1 'Student-2013',0 'Student-2012',0 StudentPrevYrTotal
UNION
SELECT 'International' Campus,0 'Weeks-2013',32 'Weeks-2012',0 'Student-2013',1 'Student-2012',1 StudentPrevYrTotal
UNION
SELECT 'International' Campus,29 'Weeks-2013',0 'Weeks-2012',6 'Student-2013',0 'Student-2012',0 StudentPrevYrTotal
UNION
SELECT 'International' Campus,27 'Weeks-2013',0 'Weeks-2012',5 'Student-2013',0 'Student-2012',0 StudentPrevYrTotal
UNION
SELECT 'International' Campus,24 'Weeks-2013',0 'Weeks-2012',4 'Student-2013',0 'Student-2012',0 StudentPrevYrTotal
UNION
SELECT 'International' Campus,0 'Weeks-2013',24 'Weeks-2012',0 'Student-2013',3 'Student-2012',4 StudentPrevYrTotal
)
Select bse.[Campus]
,[Weeks-2013]= isnull( Y2013.[Weeks-2013], 0 )
,[Weeks-2012]= isnull( Y2012.[Weeks-2012], 0 )
,[Student-2013]= isnull( Y2013.[Student-2013], 0 )
,[Student-2012]= isnull( Y2012.[Student-2012], 0 )
from ( select[Campus]
,[Weeks]= [Weeks-2013]
from CTE
where [Weeks-2013] <> 0
union
select[Campus]
,[Weeks]= [Weeks-2012]
from CTE
where [Weeks-2012] <> 0
) as bse
left outer join CTE as Y2012
on bse.Campus = Y2012.Campus
and Y2012.[Weeks-2012] = [Weeks]
left outer join CTE as Y2013
on bse.Campus = Y2013.Campus
and Y2013.[Weeks-2013] = [Weeks]
Order by isnull( Y2013.[Weeks-2013], Y2012.[Weeks-2012])
;
March 29, 2013 at 4:32 pm
use bar brackets for column name for 'week-2013' it will be [week-2013]
that is what I understand,
April 2, 2013 at 12:30 am
May be this is what you are looking for:
;With CTE
As
(
SELECT DISTINCT 'International' Campus,28 'Weeks-2013',0 'Weeks-2012',2 'Student-2013',0 'Student-2012',0 StudentPrevYrTotal
UNION
SELECT DISTINCT 'International' Campus,32 'Weeks-2013',0 'Weeks-2012',1 'Student-2013',0 'Student-2012',0 StudentPrevYrTotal
UNION
SELECT DISTINCT 'International' Campus,0 'Weeks-2013',32 'Weeks-2012',0 'Student-2013',1 'Student-2012',1 StudentPrevYrTotal
UNION
SELECT DISTINCT 'International' Campus,29 'Weeks-2013',0 'Weeks-2012',6 'Student-2013',0 'Student-2012',0 StudentPrevYrTotal
UNION
SELECT DISTINCT 'International' Campus,27 'Weeks-2013',0 'Weeks-2012',5 'Student-2013',0 'Student-2012',0 StudentPrevYrTotal
UNION
SELECT DISTINCT 'International' Campus,24 'Weeks-2013',0 'Weeks-2012',4 'Student-2013',0 'Student-2012',0 StudentPrevYrTotal
UNION
SELECT DISTINCT 'International' Campus,0 'Weeks-2013',24 'Weeks-2012',0 'Student-2013',3 'Student-2012',4 StudentPrevYrTotal
)
Select a.Campus, ISNULL(a.[Weeks-2013],0) As [Weeks-2013], ISNULL(b.[Weeks-2012],0) As [Weeks-2012],
ISNULL(a.[Student-2013],0) As [Student-2012], ISNULL(b.[Student-2012],0) As [Student-2012]
From
(
Select Campus, [Weeks-2013], [Student-2013] From CTE Where [Weeks-2013] <> 0
) As a
FULL JOIN
(
Select Campus, [Weeks-2012], [Student-2012] From CTE Where [Weeks-2012] <> 0
) As b ON a.Campus = b.Campus AND a.[Weeks-2013] = b.[Weeks-2012]
Hope this helps.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy