SO, I'm not that great with PIVOT's in SQL (as I usually do this in Excel with "flat" data), but managed to cobble together the following output:
CONTID FULLNAME %! %% %3
001 Store 1 0 0 0
002 Store 2 0 0 0
003 Store 3 0 0 0
004 Store 4 0 0 0
005 Store 5 0 0 0
(hope that displays properly)
Here is my SQL:
USE mydb
go
WITH basequery
AS (SELECT c.contid,
p.fullname,
h.keyalm
FROM customer c
LEFT JOIN clogs h
ON c.serialno = h.serialno
LEFT JOIN contact P
ON c.serialno = P.serialno
WHERE evtype = 1
AND p.conttype = 1)
SELECT *
FROM basequery
PIVOT(Count(keyalm)
FOR keyalm IN ("%!",
"%%",
"%3",
"%4",
"%6",
"%8",
"%9",
"%A",
"%B",
"%C",
"%D",
"%E",
"%F",
"%G",
"%H",
"%I",
"%K",
"%L",
"%M",
"%O",
"%P",
"%Q",
"%R",
"%S",
"%T",
"%U",
"%V",
"%W",
"%X")) AS pvt --I truncated some of this
What I'd like to is somehow not display the columns that have all 0's for their totals, like the ones displayed here. Attempted to do a "WHERE" after the pivot, but I got yelled at for that.
I've truncated the columns for SO purposes, but my output actually has like 100 columns, and majority are all 0's. Would like to eliminate those from the displayed pivot if possible. Is there a right way to do this?
Try this query for dynamic columns:
USE mydb
go
declare @str as nvarchar(max),@query as nvarchar(max);
set @str = stuff((SELECT distinct ',['+keyalm+']' FROM customer c
LEFT JOIN clogs h
ON c.serialno = h.serialno
LEFT JOIN contact P
ON c.serialno = P.serialno
WHERE evtype = 1
AND p.conttype = 1 for xml path('')),1,1,'')
set @query =
'WITH basequery
AS (SELECT c.contid,
p.fullname,
h.keyalm
FROM customer c
LEFT JOIN clogs h
ON c.serialno = h.serialno
LEFT JOIN contact P
ON c.serialno = P.serialno
WHERE evtype = 1
AND p.conttype = 1)
SELECT *
FROM basequery
PIVOT(Count(keyalm)
FOR keyalm IN ('+ @str +')) AS pvt'
execute sp_executesql @query
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다