Prev: Count over group
Next: Query
From: Bob McClellan on 30 Jul 2010 12:50
I was able to use the below code example to
get the results I was looking for.

I put this together using different examples
found googling for dynamic cross tab views.

Can someone help me understand what is happening
in The top part ... FOR XML PATH('')
What does the last part... ), 1, 2, '') + ']' mean
Any explanation / help is much appreciated.
thanks in advance,
...bob

DECLARE @listCol VARCHAR(2000)
SELECT @listCol = STUFF(( SELECT DISTINCT '],['
+ rtrim(convert(char,DateAppended,112) )

FROM gm_MonthlySnapShots

ORDER BY '],[' + rtrim(convert(char,DateAppended,112) ) desc
FOR XML PATH('')
), 1, 2, '') + ']'

--------------------------------------------------------------------
DECLARE @query VARCHAR(4000)
SET @query = 'Select *
from ( select *
From ( Select Actual,
GM,
Tab,
Metric,
ss = convert(char,DateAppended,112)
FROM gm_MonthlySnapShots SS
INNER JOIN GMS.DBO.TARGETS T ON ss.tab = t.area
and ss.metric = t.description
where t.interval = ''m'' ) G

PIVOT ( MAX(ACTUAL)
FOR SS IN ('+@listCol+'))P
)p2'

EXECUTE (@query)
From: pi on 31 Jul 2010 03:56
Hi


Without call to stuff function the result for @listcol should be for example
:
],[20100602],[20100601
stuff('],[20100602],[20100601',1,2,'') returns '[20100602],[20100601]'
which is the expected list of columns
for more have a look at STUFF documentation.
Patrick




"Bob McClellan" <bobmcc777(a)gmail.com> a �crit dans le message de news:
A99155E1-9DA2-4E3B-96C2-85A29196E259(a)microsoft.com...
>I was able to use the below code example to get the results I was looking
>for.
>
> I put this together using different examples found googling for dynamic
> cross tab views.
>
> Can someone help me understand what is happening in The top part ... FOR
> XML PATH('') What does the last part... ), 1, 2, '') + ']' mean
> Any explanation / help is much appreciated.
> thanks in advance,
> ..bob
>
> DECLARE @listCol VARCHAR(2000)
> SELECT @listCol = STUFF(( SELECT DISTINCT '],[' +
> rtrim(convert(char,DateAppended,112) )
> FROM gm_MonthlySnapShots
>
> ORDER BY '],[' + rtrim(convert(char,DateAppended,112) ) desc
> FOR XML PATH('')
> ), 1, 2, '') + ']'
>
> --------------------------------------------------------------------
> DECLARE @query VARCHAR(4000)
> SET @query = 'Select * from ( select * From ( Select Actual, GM, Tab,
> Metric, ss = convert(char,DateAppended,112) FROM gm_MonthlySnapShots SS
> INNER JOIN GMS.DBO.TARGETS T ON ss.tab = t.area
> and ss.metric = t.description
> where t.interval = ''m'' ) G
> PIVOT ( MAX(ACTUAL) FOR SS IN ('+@listCol+'))P
> )p2'
>
> EXECUTE (@query)



From: Bob McClellan on 31 Jul 2010 10:06
Got it...

I've not used the STUFF function or the FOR XML
Believe it or not... I was thinking ), 1, 2, '') + ']' was associated
with the FOR XML. Naturally, I could not find anything related
to this in BOL or online.
Checking BOL for STUFF along with your explanation makes
it perfectly clear.

Thanks Patrick.
I really appreciate the reply.
...bob


"pi" <pisambert(a)free.fr> wrote in message
news:u$rXsXIMLHA.4084(a)TK2MSFTNGP05.phx.gbl...
> Hi
>
>
> Without call to stuff function the result for @listcol should be for
> example :
> ],[20100602],[20100601
> stuff('],[20100602],[20100601',1,2,'') returns '[20100602],[20100601]'
> which is the expected list of columns
> for more have a look at STUFF documentation.

> Patrick
>
>
>
>
> "Bob McClellan" <bobmcc777(a)gmail.com> a �crit dans le message de news:
> A99155E1-9DA2-4E3B-96C2-85A29196E259(a)microsoft.com...
>>I was able to use the below code example to get the results I was looking
>>for.
>>
>> I put this together using different examples found googling for dynamic
>> cross tab views.
>>
>> Can someone help me understand what is happening in The top part ... FOR
>> XML PATH('') What does the last part... ), 1, 2, '') + ']' mean
>> Any explanation / help is much appreciated.
>> thanks in advance,
>> ..bob
>>
>> DECLARE @listCol VARCHAR(2000)
>> SELECT @listCol = STUFF(( SELECT DISTINCT '],[' +
>> rtrim(convert(char,DateAppended,112) )
>> FROM gm_MonthlySnapShots
>>
>> ORDER BY '],[' + rtrim(convert(char,DateAppended,112) ) desc
>> FOR XML PATH('')
>> ), 1, 2, '') + ']'
>>
>> --------------------------------------------------------------------
>> DECLARE @query VARCHAR(4000)
>> SET @query = 'Select * from ( select * From ( Select Actual, GM, Tab,
>> Metric, ss = convert(char,DateAppended,112) FROM gm_MonthlySnapShots SS
>> INNER JOIN GMS.DBO.TARGETS T ON ss.tab = t.area
>> and ss.metric = t.description
>> where t.interval = ''m'' ) G
>> PIVOT ( MAX(ACTUAL) FOR SS IN ('+@listCol+'))P
>> )p2'
>>
>> EXECUTE (@query)
>
>
>

 | 
Pages: 1
Prev: Count over group
Next: Query