pdd1lan
asked on
crosstab/PIVOT query
HI,
here is my query:
SELECT SRT_DT
, [A], [B1], [C]
FROM (
SELECT COUNT(ITEM_ID) AS ITEM_TOTAL
,SRT_DT
,ITEM_LOC
FROM TABLE1
WHERE SRT_DT BETWEEN '9/1/2010' AND '9/15/2010'
GROUP BY ITEM_LOC,SRT_DT
) P PIVOT
(
SUM(ITEM_TOTAL)
FOR [ITEM_LOC] IN ([A],[B1],[C])
) AS PVT
the output of the query below.
SRT_DT A B1 C
9/1/2010 1 1 1
9/2/2010 2 2 2
…
9/15/2010 15 15 15
Is there any way to have output like below instead? I would like to have the column names as srt_dt field. SRT_DT are output values from the query. is it possible?
ITEM_LOC 9/1/10 9/2/10 …9/15/10
A 1 2 15
B1 1 2 15
C 1 2 15
thanks,
pl
here is my query:
SELECT SRT_DT
, [A], [B1], [C]
FROM (
SELECT COUNT(ITEM_ID) AS ITEM_TOTAL
,SRT_DT
,ITEM_LOC
FROM TABLE1
WHERE SRT_DT BETWEEN '9/1/2010' AND '9/15/2010'
GROUP BY ITEM_LOC,SRT_DT
) P PIVOT
(
SUM(ITEM_TOTAL)
FOR [ITEM_LOC] IN ([A],[B1],[C])
) AS PVT
the output of the query below.
SRT_DT A B1 C
9/1/2010 1 1 1
9/2/2010 2 2 2
…
9/15/2010 15 15 15
Is there any way to have output like below instead? I would like to have the column names as srt_dt field. SRT_DT are output values from the query. is it possible?
ITEM_LOC 9/1/10 9/2/10 …9/15/10
A 1 2 15
B1 1 2 15
C 1 2 15
thanks,
pl
ASKER
Thanks for quick response.
I will check it out on monday
I will check it out on monday
ASKER
I got an error. ".. incorrect syntax near ')' ...
I think this statement, I am trying to understand "... 1, 2, '') + ']' ". thanks
set @cols = stuff(
(with CTE as (
select @startdate as st, @enddate as ed
union all
select st + 1, ed from CTE
where st + 1 <= ed
)
select '], [' + convert(varchar, st, 101) from CTE for xml path('')
),
1, 2, '') + ']'
I think this statement, I am trying to understand "... 1, 2, '') + ']' ". thanks
set @cols = stuff(
(with CTE as (
select @startdate as st, @enddate as ed
union all
select st + 1, ed from CTE
where st + 1 <= ed
)
select '], [' + convert(varchar, st, 101) from CTE for xml path('')
),
1, 2, '') + ']'
I missed a comma in line 22 it should be like this:
set @strSQL = 'select ITEM_LOC, ' + @cols +
ASKER
I still have an error while I tried to execute only exec(@cols)
declare @strSQL varchar(max)
declare @cols varchar(max)
declare @startdate datetime
declare @enddate datetime
set @startdate = '9/1/2010'
set @enddate = '9/15/2010'
set @cols = stuff(
(with CTE as (
select @startdate as st, @enddate as ed
union all
select st + 1, ed from CTE
where st + 1 <= ed
)
select '], [' + convert(varchar, st, 101) from CTE for xml path('')
),
1, 2, '') + ']'
here are error's message:
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ')'.
declare @strSQL varchar(max)
declare @cols varchar(max)
declare @startdate datetime
declare @enddate datetime
set @startdate = '9/1/2010'
set @enddate = '9/15/2010'
set @cols = stuff(
(with CTE as (
select @startdate as st, @enddate as ed
union all
select st + 1, ed from CTE
where st + 1 <= ed
)
select '], [' + convert(varchar, st, 101) from CTE for xml path('')
),
1, 2, '') + ']'
here are error's message:
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ')'.
try adding a semicolon there
set @cols = stuff(
(;with CTE as (
set @cols = stuff(
(;with CTE as (
ASKER
still give an error
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ')'.
or we can use another approach:
set @cols = stuff(
select '], [' + convert(varchar, st, 101) from (
select @startdate + number -1 as st
from master..spt_values where number > 0 and number <= datediff(d, @startdate, @enddate)
) a
for xml path('')
),
1, 2, '') + ']'
typo there
set @cols = stuff(
(select '], [' + convert(varchar, st, 101) from (
select @startdate + number -1 as st
from master..spt_values where number > 0 and number <= datediff(d, @startdate, @enddate)
) a
for xml path('')
),
1, 2, '') + ']'
missed something there
...
set @cols = stuff(
(select '], [' + convert(varchar, st, 101) from (
select @startdate + number -1 as st
from master..spt_values where type = 'P' and number > 0 and number <= datediff(d, @startdate, @enddate)+1
) a
for xml path('')
),
1, 2, '') + ']'
...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
excellent!! thanks for help.
ASKER
good job
check the below:
Open in new window