标签云

微信群

扫码加入我们

WeChat QR Code

I have read the stuff on MS pivot tables and I am still having problems getting this correct.I have a temp table that is being created, we will say that column 1 is a Store number, and column 2 is a week number and lastly column 3 is a total of some type. Also the Week numbers are dynamic, the store numbers are static.StoreWeek xCount----------- ------10219610111381051371091591012282102221210527810929710536010231231013220109387I would like it to come out as a pivot table, like this:Store123456....----- 101138282220102 96212123105 37109Store numbers down the side and weeks across the top.


possible duplicate of SQL Server dynamic PIVOT query?

2019年04月19日11分45秒

Very nice! But how to eliminate column when all values of that column are NULL?

2019年04月19日11分45秒

ZooZ See answer below. Haven't tried it out verbatim, but the concept is sound.

2019年04月19日11分45秒

+1 "It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-cded version initially." Unlike the Qlikview Generic function (community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic) which allows doesn't require that you explicitly name the different "FOR ____ IN (...)"

2019年04月18日11分45秒

If you are building a pivot table with a cte earlier.cte3 AS (select ... ) then you have the defined above logic with the cols and query ... there is an error.` Invalid object name 'cte3'.` how do you fix that. –

2019年04月19日11分45秒

This is fantastic - nice one bluefeet. I'd never used STUFF(...) before (or the XML PATH either). For the benefit of other readers, all that is doing is joining the column names and chopping off the leading comma.Note I think the following is slightly simpler:select cols =(SELECT DISTINCT QUOTENAME(Week) + ','from yt order by 1 FOR XML PATH(''))set cols = SUBSTRING(cols, 1, LEN(cols) - 1)... replacing the group by by distinct and order by 1 and manually chopping a suffixed comma!

2019年04月19日11分45秒

it is easier, but it is a very expensive operation, those subqueries have to be executed once for each row returned from the table.

2019年04月18日11分45秒