\nI don't know about you but I'm not in love with the Matrix control in SQL RS.\n<\/p>\n
\nI'm not in love with pivot tables in Excel, either, maybe it's me, I don't get my head around these constructs very well in general. Other people manage<\/a>. But, in RS, I just seem to spend too much time getting around the expectations of how things are supposed to work, which should make things easier but don't. \n<\/p>\n \n(RANT-ASIDE: How many times have you seen a question about how to get subtotals before rather than after the rest of the columns, by the way? Who thought up that green triangle? It's a crowded interface, it's really hard to get all that functionality in, but still<\/strong>. OK, OK, I know it's easy to Monday-morning quarterback this stuff to death — I've been on the other side, it's maddening. So I'm not really complaining, and this rant does not constitute a fair criticism.)\n<\/p>\n \nSo I like to do stuff in a table, where possible, even when I have to do a lot more work in T-SQL to get where the matrix would auto-magically put me.\n<\/p>\n \nAs a result, even though I don't like pivot tables in Excel, I'm kind of falling in love with the PIVOT clause<\/a> in SQL 2005. Like the PARTITION stuff I've already discussed<\/a>, this one really opens up (you should pardon the expression) reporting vistas.\n<\/p>\n \nThis puppy gives you the ability to rotate tables. I figure that the matrix is actually using it, or something very similar to it with one exception which I'll discuss below, under the covers. So why not do it yourself?\n<\/p>\n \nThe critical difference is dynamic expression of the columns you want. You've got to express your pivoted columns — twice, actually — within your SELECT statement. Here is a an example PIVOT query from the docs<\/a>:\n<\/p>\n \nUSE AdventureWorks \nHow can you do that when you don't know what the columns are?\n<\/p>\n \nI wrote a quick example of how to do this in T-SQL for a co-worker, unrelated to reporting. It's actually easier to do if you write the query as SQL using VB, where there are much more capable string-handling expressions than what I'm going to show you here.\n<\/p>\n \nFirst you need to have some way to determine your max number of columns. In this example, I'll be setting up my buckets by month values in a datetime column, and using DATEDIFF() from a specified date to express the meaning of each bucket (literally "how many months ago did something happen"). So I'll use DATEDIFF() in a similar but much quicker query, to figure how many buckets I have, first.\n<\/p>\n \nSET<\/span> NOCOUNT<\/span> ON \nNext, you need to set up the two clauses that reference those columns. You'll need a second variable (besides the total number of columns) that indicates some base column name that is meaningful within your data set, to which you will add an index or identifier.\n<\/p>\n \n\/* decide how you want to name the cols <\/span>\n<\/div>\n \n \n<\/p>\n \nNow you can derive the two clauses you need. This part is completely generic (and again it is much, much easier to write it in VB than in T-SQL although this should be reasonably clear). You can move it into appropriate generic functions in either .NET code or T-SQL, passing in @ColName and @TotalCols, for any PIVOT-y query you wanted to assemble:\n<\/p>\n \nDECLARE<\/span> @ThisItem <\/span>AS<\/span> <\/span>INTEGER<\/span>,<\/span> @ThisPos <\/span>AS<\/span> <\/span>INTEGER<\/span>,<\/span> \nNow you've got what you need to assemble the query and execute it: \n<\/p>\n \nSET<\/span> @Statement1 <\/span>=<\/span> <\/span>\/* order the results if desired *\/ <\/span>\/* end the statement and execute it *\/ \nYour generated query for this example, the thing that executed, looked something like this (assuming @TotalCols = 5):\n<\/p>\n \nSELECT EventType, \n \n<\/p>\n \nI figure that, underneath, the matrix has to be processing the data something like this; first figuring out the buckets and then designing either a PIVOT clause or something similar, with its dynamically-created column instructions, to execute on the data set with which you present it in the report. \n<\/p>\n \nNow you know that, for reporting purposes, this isn't the end of the story — although it solves my co-worker's problem pretty neatly. You still have to tell the report about your dynamic structures, and where you have buckets that can't be indexed as easily as "months ago", you have to provide an index value to them somehow (this is another place where you will find ROW_NUMBER() and PARTITION helpful).\n<\/p>\n \nBut that's enough for now, and I hope it gives you some ideas…<\/p>\n","protected":false},"excerpt":{"rendered":" I don't know about you but I'm not in love with the Matrix control in SQL RS. I'm not in love with pivot tables in Excel, either, maybe it's me, I don't get my head around these constructs very well in general. Other people manage. But, in RS, I just seem to spend too muchRead more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,6],"tags":[],"class_list":["post-185","post","type-post","status-publish","format-standard","hentry","category-reporting","category-sql-server"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/185","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/comments?post=185"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/185\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=185"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=185"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=185"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nGO
\nSELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
\nFROM
\n(SELECT PurchaseOrderID, EmployeeID, VendorID
\nFROM Purchasing.PurchaseOrderHeader) p
\nPIVOT<\/font>
\n(
\nCOUNT (PurchaseOrderID)
\nFOR EmployeeID IN
\n( [164], [198], [223], [231], [233] )
\n) AS pvt
\nORDER BY VendorID;\n<\/p>\n
\nDECLARE<\/span> @Today AS<\/span> DATETIME
\nSET<\/span> @Today =<\/span> GETDATE<\/span>()
\n<\/span>DECLARE<\/span> @TotalCols AS<\/span> INTEGER<\/span><\/span>
\n<\/span>DECLARE<\/span> @ColName AS<\/span> NVARCHAR<\/span>(<\/span>50)<\/span><\/span>
\nDECLARE<\/span> @Statement1 AS<\/span> NVARCHAR<\/span>(<\/span>2000),<\/span>
\n @Statement2 AS<\/span> NVARCHAR<\/span>(<\/span>2000)<\/span><\/span>
\n\/* do a query to find out your max number of colums *\/<\/span>
\n\/* You could do this differently if you don't always want
\n all the data.
\n*\/
\n<\/span>SELECT<\/span> @TotalCols <\/span>=<\/span>
\n<\/span> COUNT<\/span>(<\/span>DISTINCT<\/span> <\/span>DATEDIFF<\/span>(<\/span>month<\/span>,E<\/span>ventDate<\/span>,<\/span>@Today<\/span>))<\/span>
\n<\/span> FROM<\/span> EventTable<\/span>\n<\/p>\n
\n in this specific query *\/
\n<\/span>SET<\/span> @ColName <\/span>=<\/span> <\/span>'MonthsAgoGrp'<\/span>\n<\/p>\n
\n @sThisItem <\/span>AS<\/span> <\/span>NVARCHAR<\/span>(<\/span>10<\/span>)<\/span>
\n<\/span>SET<\/span> @Statement1 <\/span>=<\/span> <\/span>'[0] AS ['<\/span> <\/span>+<\/span> @ColName <\/span>+<\/span> <\/span>'0]'
\n<\/span>SET<\/span> @Statement2 <\/span>=<\/span> <\/span>'[0]'
\n<\/span>SET<\/span> @ThisItem <\/span>=<\/span> 1
\n<\/span>WHILE<\/span> @ThisItem <\/span><<\/span> @TotalCols
\n<\/span> BEGIN
\n SET<\/span> @sThisItem <\/span>=<\/span> <\/span>CAST<\/span>(<\/span>@ThisItem <\/span>AS<\/span> <\/span>NVARCHAR<\/span>)
\n<\/span> SET<\/span> @Statement1 <\/span>=<\/span> @Statement1 <\/span>+<\/span> <\/span>',['<\/span> <\/span>+<\/span>
\n @sThisItem <\/span>+<\/span>'] AS ['<\/span> <\/span>+<\/span> @ColName <\/span>+<\/span> @sThisItem <\/span>+<\/span> <\/span>']'
\n<\/span> SET<\/span> @Statement2 <\/span>=<\/span> @Statement2 <\/span>+<\/span> <\/span>',['<\/span> <\/span>+<\/span>
\n @sThisItem <\/span>+<\/span>']'
\n<\/span> SET<\/span> @ThisItem <\/span>=<\/span> @ThisItem <\/span>+<\/span> 1
\n<\/span> END<\/span> <\/span>
\n\/* You can print out @Statement1 and @Statement2 at this point,
\n and verify that they are in fact the two clauses in the PIVOT
\n statement that we needed to create differently for different
\n numbers of columns
\n*\/<\/span>\n<\/p>\n
\n N<\/span>' SELECT EventType, '<\/span> <\/span>+<\/span> @Statement1 <\/span>+<\/span>
\n N<\/span>' FROM '
\n<\/span>\/* your pivot-able query here: *\/
\n<\/span> SET<\/span> @Statement1 <\/span>=<\/span> @Statement1 <\/span>+<\/span>
\n N<\/span>'(SELECT EventType,
\n DATEDIFF(month,EventDate,'''<\/span> <\/span>+<\/span>
\n<\/span> CAST<\/span>(<\/span>@Today <\/span>AS<\/span> <\/span>NVARCHAR<\/span>)<\/span> <\/span>+
\n<\/span> N<\/span>''') AS MonthsAgo, EventID
\n FROM EventTable ) SourceTable '
\n<\/span>
\n<\/span>\/* add the pivot aggregation function of your choice *\/
\n<\/span>SET<\/span> @Statement1 <\/span>=<\/span> @Statement1 <\/span>+
\n<\/span> N<\/span>' PIVOT '<\/span> <\/span>+<\/span>
\n N<\/span>'(COUNT(EventID) FOR MonthsAgo IN ('<\/span>
\n<\/span>
\n<\/span>\/* our second generated clause here *\/
\n<\/span>SET<\/span> @Statement1 <\/span>=<\/span>
\n @Statement1 <\/span>+<\/span> @Statement2 <\/span>+<\/span> N<\/span>')) AS PivotTable '<\/p>\n
\n<\/span>SET<\/span> @Statement1 <\/span>=<\/span> @Statement1 <\/span>+<\/span> N<\/span>' ORDER BY EventType '<\/p>\n
\n<\/span>SET<\/span> @Statement1 <\/span>=<\/span> @Statement1 <\/span>+<\/span> N<\/span>';'
\n<\/span>EXEC<\/span> <\/span>sp_executeSQL<\/span> @Statement1<\/span>\n<\/p>\n
\n [0] AS MonthsAgoGrp0, [1] AS MonthsAgoGrp1,
\n [2] AS MonthsAgoGrp2, [3] AS MonthsAgoGrp3, [4] AS MonthsAgoGrp4
\nFROM
\n(SELECT EventType,
\n DATEDIFF(month,EventDate,'8\/17\/2007') AS MonthsAgo, EventID
\n FROM EventTable ) SourceTable
\nPIVOT
\n(
\n COUNT(EventID)
\n FOR MonthsAgo IN
\n ([0],[1],[2],[3],[4])
\n) AS PivotTable
\nORDER BY EventType ;\n<\/p>\n