How do i line up the weeks in T-SQL ?

Question

Post reply

How do i line up the weeks in T-SQL ?

kevin_nikolai

SSCarpal Tunnel

Points: 4343
More actions
March 27, 2013 at 1:52 pm

#272158

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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/

Lowell SSC Guru Points: 323518 More actions · Answer 2

...ORDER BY ?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 3

Ho Sean & Lowell, updated my post - hope you will now understand what I am asking ?

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

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/

Steve JP Ten Centuries Points: 1070 More actions · Answer 5

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])

;

umarrizwan Old Hand Points: 374 More actions · Answer 6

use bar brackets for column name for 'week-2013' it will be [week-2013]

that is what I understand,

😀

vinu512 SSCoach Points: 15729 More actions · Answer 7

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. 🙂

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉