September 11, 2018 at 2:28 pm
I have a pivot query as below. This pivots the data in ev as column and calculates count.
Some data in column ev contains values such as, A & B, X & T.
These are coming up in column header as A amp; B, X amp; T. How do I fix this? Thanks.
select ec, ev, er,
count(ee) as ct
into #temp
from master m
group by ec, ev, er
DECLARE @cols VARCHAR(8000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + cast(ev as nvarchar(100))
FROM #temp AS t2
ORDER BY '],[' + cast(ev as nvarchar(100))
FOR XML PATH('')), 1, 2, '') + ']'
DECLARE @query VARCHAR(8000)
SET @query = '
SELECT * FROM #temp
PIVOT
(
sum([ct])
FOR [ev]
IN (' + @cols + ')
)
AS p '
EXECUTE(@query)
September 11, 2018 at 3:47 pm
You probably need the value function for XML data on the end of the query, and possibly adding ,TYPE to the end of FOR XML PATH(''). I can't recall the exact syntax of the .value() specification that can give you text output instead of XML output. You can probably Google that though.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 12, 2018 at 8:21 am
You haven't followed the rules for XML concatenation. Try the following instead.
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + cast(ev as nvarchar(100))
FROM #temp AS t2
ORDER BY '],[' + cast(ev as nvarchar(100))
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(250)'), 1, 2, '') + ']'
What's happening is that & is a reserved character in XML, so the XML PATH is entitizing those characters, that is, it's transforming those characters so that they won't be mistaken for the reserved characters. You need to transform them back, which is what the value() function does.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 12, 2018 at 12:26 pm
Thanks all.
I used the below.
select ec, ev, er,
count(ee) as ct
into #temp
from master m
group by ec, ev, er
DECLARE @cols VARCHAR(8000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + cast(ev as nvarchar(max))
FROM #temp AS t2
ORDER BY '],[' + cast(ev as nvarchar(max))
FOR XML PATH(''), TYPE).value(',', 'nvarchar(max)'), 1, 2, '') + ']'
DECLARE @query VARCHAR(8000)
SET @query = '
SELECT * FROM #temp
PIVOT
(
sum([ct])
FOR [ev]
IN (' + @cols + ')
)
AS p '
EXECUTE(@query)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply