Help with PIVOT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with PIVOT

Author

Topic

paulnamroud
Starting Member

26 Posts

Posted - 2014-11-04 : 16:34:24

Hello,

I have a very complicated situation where I need to use a PIVOT statement on a vertical table and I would like to ask you help in order to achieve my objective.
I'm open to any other solution.

My objective to create a stored procedure procedure with 2 parameters: Year & Month defined by user.

Then, I need my SQL statement return vertical Data into a very well formatted result:
- Based on the following example, this statement will return 5 different working weeks for the defined Year & Month
- For each week, this statement should return the list of information attached to it
- The Header of each week should be displayed as the following:
Apr 28 - May 04
May 05 - May 11
May 12 - May 18
May 19 - May 25
May 26 - June 01
- The header should contain the number of records / information per week. The value is presented inside "()"

Thank you for your help

Paul

Information Transaction_Date
--------------- ----------------
Information # 1 2014-04-28
Information # 2 2014-04-28
Information # 3 2014-04-28
Information # 4 2014-04-28
Information # 5 2014-04-29
Information # 6 2014-04-29
Information # 7 2014-04-29
Information # 8 2014-04-30
Information # 9 2014-04-30
Information # 10 2014-05-01
Information # 11 2014-05-01

Information # 12 2014-05-05
Information # 13 2014-05-05
Information # 14 2014-05-06
Information # 15 2014-05-06
Information # 16 2014-05-07
Information # 17 2014-05-07
Information # 18 2014-05-07
Information # 19 2014-05-08

Information # 20 2014-05-12
Information # 21 2014-05-12
Information # 22 2014-05-14
Information # 23 2014-05-14
Information # 24 2014-05-14
Information # 25 2014-05-14
Information # 26 2014-05-14
Information # 27 2014-05-16
Information # 28 2014-05-16
Information # 29 2014-05-16

Information # 30 2014-05-19
Information # 31 2014-05-19
Information # 32 2014-05-22
Information # 33 2014-05-22
Information # 34 2014-05-23
Information # 35 2014-05-24
Information # 36 2014-05-24

Information # 37 2014-05-26
Information # 38 2014-05-26
Information # 39 2014-05-26
Information # 40 2014-05-26
Information # 41 2014-05-26
Information # 42 2014-05-28
Information # 43 2014-05-28
Information # 44 2014-05-28
Information # 45 2014-05-29
Information # 46 2014-05-29
Information # 47 2014-05-29
Information # 48 2014-05-30
Information # 49 2014-05-31

Apr 28 - May 04 (11) May 05 - May 11 (8) May 12 - May 18 (10) May 19 - May 25 (7) May 26 - June 01 (13)
-------------------- ------------------- -------------------- -------------------- --------------------
Information # 1 Information # 12 Information # 20 Information # 30 Information # 37
Information # 2 Information # 13 Information # 21 Information # 31 Information # 38
Information # 3 Information # 14 Information # 22 Information # 32 Information # 39
Information # 4 Information # 15 Information # 23 Information # 33 Information # 40
Information # 5 Information # 16 Information # 24 Information # 34 Information # 41
Information # 6 Information # 17 Information # 25 Information # 35 Information # 42
Information # 7 Information # 18 Information # 26 Information # 36 Information # 43
Information # 8 Information # 19 Information # 27 Information # 44
Information # 9 Information # 28 Information # 45
Information # 10 Information # 29 Information # 46
Information # 11 Information # 47
Information # 48
Information # 49

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-11-04 : 19:34:30

[code]
;with cte as
(
select Information,
col = case when Transaction_Date between '2014-04-28' and '2014-05-04' then 1
when Transaction_Date between '2014-05-05' and '2014-05-11' then 2
when Transaction_Date between '2014-05-12' and '2014-05-18' then 3
when Transaction_Date between '2014-05-19' and '2014-05-25' then 4
when Transaction_Date between '2014-05-26' and '2014-06-01' then 5
end
from yourtable
),
cte2 as
(
select Information, col,
row = row_number() over (partition by col order by Information)
from cte
)
select [1] as [Apr 18 - May 04],
[2] as [May 05 - May 11],
[3] as [May 12 - May 18],
[4] as [May 19 - May 25],
[5] as [May 26 - Jun 01]
from cte2
pivot
(
max(Information)
for col in ([1], [2], [3], [4], [5])
) p[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources