Pivot Query help

Question

Post reply

Pivot Query help

joshtheflame

Default port

Points: 1499
More actions
January 7, 2011 at 4:52 am

#231845

I have following tables
Hub_Details
Branch_ID
Branch_Name
VTRCheckList
CLid
CLName
VTRCheckListDetails
CLid
Branch_ID
VtrValue
ResponseDate
This is what i am doing.
SELECT *
FROM
(
SELECT c.CLName, b.BranchName, SUM(CAST(VtrValue as int)) as 'vtr' FROM dbo.VTRCheckListDetails cd
JOIN dbo.VTRCheckList c ON c.CLid = cd.CLid
JOIN dbo.Hub_Details b ON b.BranchID = cd.BranchID
where Convert(date, cd.vtrRespDate, 105) >= convert(date,'06-01-2011',105) and Convert(date, cd.vtrRespDate, 105) <= convert(date,'06-01-2011',105)
group by c.CLName, b.BranchName
) a
PIVOT
(
SUM(vtr) FOR BranchName IN
(
[BR1], [BR2], [BR3], [BR4], [BR5], [BR6],
[BR7], [BR8], [BR9], [BR10], [BR11], [BR12]
)
) b
Well at the end if you notice i have put [BR1], [BR2] and results are coming fine but the problem is that i dont want to fix branch names here as more branches comes and add in master table so it requires frequent change in the sql script. so is it possible to build columns using one select here (select branch_name from Hub_Details" sort of rather hard code the branches.

Viewing 3 posts - 1 through 2 (of 2 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please have a look at the DynamicCrossTab article referenced in my signature.

It seems like exactly what you need...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

davidandrews13 SSCertifiable Points: 6689 More actions · Answer 2

I think I got this way of doing it from SQLServerCEntral.

Unfortunately i cant remember the name of the person to give them a name-check.

declare @pivot_columns table(pivot_column varchar(100))

insert into @pivot_columns

select ID from YourTableName

declare @pivot varchar(max)

select @pivot = coalesce(@pivot+' , ',' ' ) + '[' + isnull(pivot_column,'') + ']' from @pivot_columns

declare @sql nchar(1000) =

'SELECT *

FROM

(

SELECT c.CLName, b.BranchName, SUM(CAST(VtrValue as int)) as "vtr" FROM dbo.VTRCheckListDetails cd

JOIN dbo.VTRCheckList c ON c.CLid = cd.CLid

JOIN dbo.Hub_Details b ON b.BranchID = cd.BranchID

where Convert(date, cd.vtrRespDate, 105) >= convert(date,"06-01-2011",105) and Convert(date, cd.vtrRespDate, 105) <= convert(date,"06-01-2011",105)

group by c.CLName, b.BranchName

) a

PIVOT

(

SUM(vtr) FOR BranchName IN

(

'+

@pivot

+

'

)

'

exec sp_executesql @sql