{"id":286,"date":"2007-07-22T00:00:00","date_gmt":"2007-07-22T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/crosstab-pivot-table-workbench\/"},"modified":"2021-09-29T16:22:17","modified_gmt":"2021-09-29T16:22:17","slug":"crosstab-pivot-table-workbench","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/crosstab-pivot-table-workbench\/","title":{"rendered":"Crosstab Pivot-table Workbench"},"content":{"rendered":"<p><style type=\"text\/css\">#MyCrosstab {\n    BORDER-RIGHT: #d3d3d3 1px solid; PADDING-RIGHT: 8px; BORDER-TOP: #d3d3d3 1px solid; PADDING-LEFT: 8px; FONT-SIZE: 12px; FLOAT: left; PADDING-BOTTOM: 8px; BORDER-LEFT: #d3d3d3 1px solid; MARGIN-RIGHT: 12px; PADDING-TOP: 8px; BORDER-BOTTOM: #d3d3d3 1px solid; FONT-FAMILY: Arial, Helvetica, sans-serif\n}\n#MyCrosstab TABLE {\n    border-style: none;\n}\n#MyCrosstab TD {\n    PADDING-RIGHT: 10px; PADDING-LEFT: 10px; FONT-SIZE: 12px; PADDING-BOTTOM: 2px; PADDING-TOP: 3px; border-style: none;\n}\n#MyCrosstab TD.number {\n    TEXT-ALIGN: right\n}\n#MyCrosstab TD.rowhead {\n    BORDER-RIGHT: #828282 1px dotted; FONT-WEIGHT: bold\n}\n#MyCrosstab TH {\n    FONT-SIZE: 12px; BORDER-BOTTOM: #828282 1px dotted; TEXT-ALIGN: center; border-style: none; border-bottom-style: solid; background-color: #fff;\n}\n#MyCrosstab .sum {\n    BORDER-TOP: #828282 2px solid\n}\n#MyCrosstab .sumrow {\n    TEXT-ALIGN: right\n}\n#MyCrosstab .total {\n    BORDER-LEFT: #828282 1px solid\n}\n.CodeInText {\n    FONT-WEIGHT: bold; FONT-FAMILY: courier new, courier, monospaced\n}\n.ImportantWords {\n    FONT-WEIGHT: bold\n}\n.ScreenText {\n    FONT-FAMILY: Century Gothic, Tahoma, Lucida Sans, sans-serif\n}\nPRE {\n    BACKGROUND-COLOR: #d6d6d6\n}\n.CodeInText {\n    FONT-WEIGHT: bold; FONT-FAMILY: courier new, courier, monospaced\n}\n.ImportantWords {\n    FONT-WEIGHT: bold\n}\n.ScreenText {\n    FONT-FAMILY: Century Gothic, Tahoma, Lucida Sans, sans-serif\n}\nPRE {\n    BACKGROUND-COLOR: #d6d6d6\n}\n.CodeInText {\n    FONT-WEIGHT: bold; FONT-FAMILY: courier new, courier, monospaced\n}\n.ImportantWords {\n    FONT-WEIGHT: bold\n}\n.ScreenText {\n    FONT-FAMILY: Century Gothic, Tahoma, Lucida Sans, sans-serif\n}\nPRE {\n    BACKGROUND-COLOR: #d6d6d6\n}\n.CodeInText {\n    FONT-WEIGHT: bold; FONT-FAMILY: courier new, courier, monospaced\n}\n.ImportantWords {\n    FONT-WEIGHT: bold\n}\n.ScreenText {\n    FONT-FAMILY: Century Gothic, Tahoma, Lucida Sans, sans-serif\n}\nPRE {\n    BACKGROUND-COLOR: #d6d6d6\n}\n.CodeInText {\n    FONT-WEIGHT: bold; FONT-FAMILY: courier new, courier, monospaced\n}\n.ImportantWords {\n    FONT-WEIGHT: bold\n}\n.ScreenText {\n    FONT-FAMILY: Century Gothic, Tahoma, Lucida Sans, sans-serif\n}\nPRE {\n    BACKGROUND-COLOR: #d6d6d6\n}\n.CodeInText {\n    FONT-WEIGHT: bold; FONT-FAMILY: courier new, courier, monospaced\n}\n.ImportantWords {\n    FONT-WEIGHT: bold\n}\n.ScreenText {\n    FONT-FAMILY: Century Gothic, Tahoma, Lucida Sans, sans-serif\n}\nPRE {\n    BACKGROUND-COLOR: #d6d6d6\n}\n.CodeInText {\n    FONT-WEIGHT: bold; FONT-FAMILY: courier new, courier, monospaced\n}\n.ImportantWords {\n    FONT-WEIGHT: bold\n}\n.ScreenText {\n    FONT-FAMILY: Century Gothic, Tahoma, Lucida Sans, sans-serif\n}\nPRE {\n    BACKGROUND-COLOR: #d6d6d6\n}\n<\/style>\n<\/p>\n<p>There comes a time with many Database Developers charged with doing management reports when the process of doing it properly gets very tedious. By &#8216;doing it properly&#8217;, I mean the &#8216;best practice&#8217; of having to do the basic reporting in SQL and relying on a front-end application to do the presentation. This is particularly true where the management want simple aggregate reports, or &#8216;Pivot-table&#8217; reports. Presentation is so closely tied with the data that splitting the process can sometimes lead to more problems than it solves. Of course, we have Reporting Services, Analysis Services and other external tools, but there are times when a simple solution based in TSQL has the upper hand.<\/p>\n<p>Anyone who was weaned on Excel knows that these pivot tables are dead simple. You select your data, bang the button and with a bit of dragging and dropping, there it is. Why, they ask, is it so hard to get it out of the database? Why so hard to make changes?<\/p>\n<p>What they want to see is something like this (using NorthWind so those stuck with SQL 2000 can join in)<\/p>\n<div id=\"MyCrosstab\">\n<h3>No. Sales per year<\/h3>\n<table>\n<thead>\n<tr class=\"header\">\n<th>\u00a0<\/th>\n<th>1996<\/th>\n<th>1997<\/th>\n<th>1998<\/th>\n<th>Total<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td class=\"rowhead\">Margaret Peacock<\/td>\n<td class=\"number\">31<\/td>\n<td class=\"number\">81<\/td>\n<td class=\"number\">44<\/td>\n<td class=\"number total\">156<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Janet Leverling<\/td>\n<td class=\"number\">18<\/td>\n<td class=\"number\">71<\/td>\n<td class=\"number\">38<\/td>\n<td class=\"number total\">127<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Nancy Davolio<\/td>\n<td class=\"number\">26<\/td>\n<td class=\"number\">55<\/td>\n<td class=\"number\">42<\/td>\n<td class=\"number total\">123<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Laura Callahan<\/td>\n<td class=\"number\">19<\/td>\n<td class=\"number\">54<\/td>\n<td class=\"number\">31<\/td>\n<td class=\"number total\">104<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Andrew Fuller<\/td>\n<td class=\"number\">16<\/td>\n<td class=\"number\">41<\/td>\n<td class=\"number\">39<\/td>\n<td class=\"number total\">96<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Robert King<\/td>\n<td class=\"number\">11<\/td>\n<td class=\"number\">36<\/td>\n<td class=\"number\">25<\/td>\n<td class=\"number total\">72<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Michael Suyama<\/td>\n<td class=\"number\">15<\/td>\n<td class=\"number\">33<\/td>\n<td class=\"number\">19<\/td>\n<td class=\"number total\">67<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Anne Dodsworth<\/td>\n<td class=\"number\">5<\/td>\n<td class=\"number\">19<\/td>\n<td class=\"number\">19<\/td>\n<td class=\"number total\">43<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Steven Buchanan<\/td>\n<td class=\"number\">11<\/td>\n<td class=\"number\">18<\/td>\n<td class=\"number\">13<\/td>\n<td class=\"number total\">42<\/td>\n<\/tr>\n<tr>\n<td class=\"sumrow rowhead\">Sum<\/td>\n<td class=\"sum number\">152<\/td>\n<td class=\"sum number\">408<\/td>\n<td class=\"sum number\">270<\/td>\n<td class=\"sum number total\">830<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>Now, you&#8217;ll notice that we&#8217;ve taken a bit of trouble to add some formatting. In the average business, they&#8217;re fussy about such things as the alignment of numbers and the clear delineation of totals, and summary lines. It also is easier on the eye. It is therefore handy to format pivot table reports in HTML. We can send them via email them, straight from SQL Server, all in their correct formatting, or put the reports on an intranet and update them daily or hourly. With the contents of this workbench we show you how it is done, and how easy it is to do, all without a .NET programmer in sight!<\/p>\n<p>However, first things first, we must first show you how to do a crosstab, or pivot table in Transact SQL<\/p>\n<h2 class=\"clearfix\">Crosstabs and Pivot tables<\/h2>\n<p>The basic code to do the report is pretty simple.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"><code>SELECT  \r\n  [No. Sales per year]=CASE WHEN row IS NULL THEN 'Sum'  \r\n                   ELSE CONVERT(VARCHAR(80),[row]) END , \r\n  [1996] =SUM( CASE col WHEN '1996' THEN data ELSE 0 END ), \r\n  [1997] =SUM( CASE col WHEN '1997' THEN data ELSE 0 END ), \r\n  [1998] =SUM( CASE col WHEN '1998' THEN data ELSE 0 END ), \r\n  [Total]= SUM( data ) \r\nFROM  \r\n   (SELECT [row]=firstname+' '+lastname,  \r\n           [col]=YEAR(OrderDate),  \r\n           [data]=COUNT(*) \r\n    FROM Employees INNER JOIN Orders  \r\n    ON (Employees.EmployeeID=Orders.EmployeeID)  \r\n    GROUP BY firstname+' '+lastname, YEAR(OrderDate) \r\n    )f \r\nGROUP BY row WITH ROLLUP \r\nORDER BY GROUPING(row),total DESC<\/code><\/pre>\n<p>You&#8217;ll notice that the years are hard-coded into the column headings, which are a time bomb waiting to happen. You&#8217;ll also realize that the all-important formatting is missing. The structure of the query seems slightly more complicated than necessary, but you&#8217;ll see why soon. The PIVOT operator in SQL Server 2005 makes it rather easier but we wanted to make this relevant to the SQL Server 2000 users too<\/p>\n<p>For any sort of portable solution that will work on SQL Server 2000, dynamic SQL is the traditional solution. Basically, the stored procedure generates the tedious code and then executes it. Why, one wonders. This is because there are a number of tweaks that have to be made, such as the order of the columns, and rows. After all, wouldn&#8217;t someone want the report ordered by the number of sales of the salesman rather than just alphabetic order? The same basic query may generate a lot of different aggregations. Pretty soon, some sort of automation will be required.<\/p>\n<p>Keith Fletcher contributed to Simple-Talk the ingenious but complex stored procedure that did cross-tabs in his excellent article <a href=\"http:\/\/www.simple-talk.com\/sql\/t-sql-programming\/creating-cross-tab-queries-and-pivot-tables-in-sql\/\">Creating cross tab queries and pivot tables in SQL.<\/a> Because we were awed by its grandeur, we didn&#8217;t initially want to add our own contribution. However, we had two another objectives, firstly to show how easy the technique can be, and also because we wanted to do more, encouraging you to try things out, and secondly, because we wanted to show how one might mark up the presentation of the crosstab.<\/p>\n<p>Here is a stored procedure that does the trick, along with some examples using NorthWind. (if you are stuck with SQL Server 2000, make the Varchar(MAX)s into Varchar(8000) and don&#8217;t be too ambitious with the complexity of your crosstabs!)<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"><code>------------------------------------------------------------------------- \r\nCREATE PROCEDURE spDynamicCrossTab \r\n\r\n@RowValue VARCHAR(255),         --what is the SQL for the row title  \r\n@ColValue VARCHAR(255),         --what is the SQL for the column title \r\n@Aggregate VARCHAR(255),        --the aggregation value to go in the cells \r\n@FromExpression VARCHAR(8000),              --the FROM, ON and WHERE clause \r\n@colOrderValue VARCHAR (255)=NULL,            --how the columns are ordered \r\n@Title VARCHAR(80)='_',    --the title to put in the first col of first row \r\n@SortBy VARCHAR(255)='row asc', --what you sort the rows by (column heading) \r\n@RowSort VARCHAR(80)=NULL, \r\n@ReturnTheDDL INT=0,--return the SQL code rather than execute it \r\n@Debugging INT=0    --debugging mode \r\n\/* \r\ne.g. \r\nExecute spDynamicCrossTab \r\n    @RowValue='firstname+'' ''+lastname', \r\n    @ColValue='Year(OrderDate)', \r\n    @Aggregate= 'count(*)', \r\n    @FromExpression='FROM Employees INNER JOIN Orders  \r\n    ON (Employees.EmployeeID=Orders.EmployeeID)', \r\n    @ColOrderValue='Year(OrderDate)', \r\n   @Title ='No. Sales per year', \r\n   @SortBy ='total desc' --what you sort the rows by (column heading) \r\n\r\nExecute spDynamicCrossTab \r\n    @RowValue='firstname+'' ''+lastname', \r\n    @ColValue='DATENAME(month,orderDate)', \r\n    @Aggregate= 'sum(subtotal)', \r\n    @FromExpression='FROM Orders  \r\n   INNER JOIN \"Order Subtotals\"  \r\n       ON Orders.OrderID = \"Order Subtotals\".OrderID \r\n   inner join employees on employees.EmployeeID =orders.EmployeeID', \r\n    @ColOrderValue='datepart(month,orderDate)', \r\n   @Title ='Customers orders per month ' \r\n\r\nEXECUTE spDynamicCrossTab  \r\n    @RowValue='country', \r\n    @ColValue='datename(quarter,orderdate) \r\n     +case datepart(quarter,orderdate)  \r\n         when 1 then ''st''  \r\n         when 2 then ''nd''  \r\n         when 3 then ''rd''  \r\n         when 4 then ''th'' end', \r\n    @Aggregate= 'sum(subtotal)', \r\n    @FromExpression='FROM Orders  \r\n   INNER JOIN \"Order Subtotals\"  \r\n       ON Orders.OrderID = \"Order Subtotals\".OrderID \r\n  inner join customers on customers.customerID =orders.customerID', \r\n    @ColOrderValue='datepart(quarter,orderDate)', \r\n   @sortby='total desc', \r\n   @Title ='value of orders per quarter' \r\n\r\n*\/ \r\nAS \r\nSET nocount ON \r\nDECLARE @Command NVARCHAR(MAX) \r\nDECLARE @SQL VARCHAR(MAX) \r\n--make sure we have sensible defaults for orders \r\nSELECT @ColOrderValue=COALESCE(@ColOrderValue, @ColValue), \r\n   @Sortby=COALESCE(@SortBy,@RowValue), \r\n    @rowsort=COALESCE(@RowSort,@RowValue) \r\n--first construct tha SQL which is used to calculate the columns in a  \r\n--string \r\nSELECT @Command='select @SQL=coalesce(@SQL,''SELECT  \r\n  ['+@Title+']=case when row is null then ''''Sum''''  \r\nelse convert(Varchar(80),[row]) end , \r\n'')+ \r\n  ''[''+convert(varchar(100),' \r\n   +@ColValue+')+''] =sum( CASE col WHEN ''''''+convert(varchar(100),' \r\n   +@ColValue+')+'''''' THEN data else 0 END ), \r\n'' '+@FromExpression+' \r\nGROUP BY '+@ColValue+' \r\norder by max('+@ColorderValue+')' \r\n--Now we execute the string to obtain the SQL that we will use for the \r\n--crosstab query \r\nEXECUTE sp_ExecuteSQL @command,N'@SQL VARCHAR(MAX) OUTPUT',@SQL OUTPUT \r\n  IF @@error &gt; 0 --display the string if there is an error \r\n    BEGIN \r\n      RAISERROR ( 'offending code was ...%s', 0, 1, @command ) \r\n      RETURN 1 \r\n    END \r\nIF @debugging &lt;&gt;0 SELECT @Command \r\n--we now add the rest of the SQL into the string \r\nSELECT @SQL=@SQL+'  [Total]= sum( data ) \r\nfrom  \r\n   (select [row]='+@RowValue+',  \r\n           [col]='+@ColValue+',  \r\n           [data]='+@Aggregate+', \r\n           [sort]=max('+@rowsort+') \r\n '+@FromExpression+'  \r\n    GROUP BY '+@RowValue+', '+@ColValue+' \r\n)f \r\ngroup by row with rollup \r\norder by grouping(row),'+@Sortby \r\n--and execute it \r\nIF @ReturnTheDDL&lt;&gt;0 SELECT @SQL ELSE EXECUTE (@SQL) \r\n  IF @@error &gt; 0  \r\n    BEGIN \r\n      RAISERROR ( 'offending code was ...%s', 0, 1, @sql ) \r\n      RETURN 1 \r\n    END \r\n\r\n\r\n\r\n<\/code><\/pre>\n<p>You&#8217;ll see that this is a developer&#8217;s tool. It is easy to crash the procedure by putting in bad SQL. SQL Injectors would love it. No sir, this is a back-office report-generating tool. Let&#8217;s try it out:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"><code>\r\nEXECUTE spDynamicCrossTab \r\n    @RowValue='ProductName', \r\n    @ColValue='Year(OrderDate)', \r\n    @Aggregate= 'ROUND(SUM(CONVERT(decimal(14, 2), OD.Quantity  \r\n* ( 1 - OD.Discount ) * OD.UnitPrice)),0)', \r\n    @FromExpression='FROM    [Order Details] OD, \r\n        Orders O, \r\n        Products P, \r\n        Categories C \r\nwhere   OD.OrderID = O.OrderID  \r\nAND OD.ProductID = P.ProductID  \r\nAND P.CategoryID = C.CategoryID', \r\n   @Title ='Customers total orders per year' \r\n\r\n-- change the line...     @RowValue='ProductName',  \r\n--             ...to     @RowValue='CategoryName', \r\n-- and see what happens! \r\n\/*-------------------------------------------------------------------\r\n<\/code><\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/414-pivot1.jpg\" alt=\"414-pivot1.jpg\" \/><\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"><code>\r\n*\/ \r\n-- \r\n-- add the row ... \r\n-- @SortBy ='total desc', --what you sort the rows by (column heading) \r\n-- before the @Title ='Customers total orders per year'-- Neat Huh? \r\n--now change  \r\n--    @RowValue='CategoryName', \r\n--    @ColValue='Year(OrderDate)', \r\n--to \r\n--    @ColValue='CategoryName', \r\n--    @RowValue='Year(OrderDate)', \r\n--Instant Rotation! \r\n--Now try this, and notice how we get the columns and rows in the right order \r\nEXECUTE spDynamicCrossTab \r\n    @colValue='DATENAME(year,orderDate)', \r\n    @rowValue='DATENAME(month,orderDate)', \r\n    @Aggregate= 'sum(subtotal)', \r\n   @Rowsort='DATEpart(month,orderDate)', \r\n    @FromExpression='FROM Orders  \r\n   INNER JOIN \"Order Subtotals\"  \r\n       ON Orders.OrderID = \"Order Subtotals\".OrderID \r\n   inner join employees on employees.EmployeeID =orders.EmployeeID', \r\n    @ColOrderValue='datepart(year,orderDate)', \r\n   @Title ='Customers orders per month ', \r\n   @sortby='max(sort) asc' \r\n\/*-------------------------------------------------------------------\r\n<\/code><\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/414-pivot2.jpg\" alt=\"414-pivot2.jpg\" \/><\/p>\n<h2>HTML Crosstabs<\/h2>\n<p>Why bother with this when the Internet abounds with such Cross tab or pivot-table procedures? This is because we are going to take it one stage further so that, instead of just producing a resultset, we want to show how to produce HTML to produce the chart like the one at the beginning of the article.<\/p>\n<p>To reproduce this&#8230;<\/p>\n<div id=\"MyCrosstab\">\n<h3>value of orders per quarter<\/h3>\n<table>\n<thead>\n<tr class=\"header\">\n<th>\u00a0<\/th>\n<th>1st<\/th>\n<th>2nd<\/th>\n<th>3rd<\/th>\n<th>4th<\/th>\n<th>Total<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td class=\"rowhead\">USA<\/td>\n<td class=\"number\">$81364.94<\/td>\n<td class=\"number\">$50525.40<\/td>\n<td class=\"number\">$58047.56<\/td>\n<td class=\"number\">$55646.73<\/td>\n<td class=\"number total\">$245584.63<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Germany<\/td>\n<td class=\"number\">$66823.20<\/td>\n<td class=\"number\">$64681.22<\/td>\n<td class=\"number\">$42550.91<\/td>\n<td class=\"number\">$56229.29<\/td>\n<td class=\"number total\">$230284.62<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Austria<\/td>\n<td class=\"number\">$32357.82<\/td>\n<td class=\"number\">$37346.79<\/td>\n<td class=\"number\">$17383.60<\/td>\n<td class=\"number\">$40915.63<\/td>\n<td class=\"number total\">$128003.84<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Brazil<\/td>\n<td class=\"number\">$47027.15<\/td>\n<td class=\"number\">$12127.25<\/td>\n<td class=\"number\">$22537.77<\/td>\n<td class=\"number\">$25233.60<\/td>\n<td class=\"number total\">$106925.77<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">France<\/td>\n<td class=\"number\">$31085.27<\/td>\n<td class=\"number\">$11225.97<\/td>\n<td class=\"number\">$14407.11<\/td>\n<td class=\"number\">$24639.96<\/td>\n<td class=\"number total\">$81358.31<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">UK<\/td>\n<td class=\"number\">$21302.05<\/td>\n<td class=\"number\">$17061.85<\/td>\n<td class=\"number\">$2292.70<\/td>\n<td class=\"number\">$18314.72<\/td>\n<td class=\"number total\">$58971.32<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Venezuela<\/td>\n<td class=\"number\">$21186.40<\/td>\n<td class=\"number\">$11991.60<\/td>\n<td class=\"number\">$12098.75<\/td>\n<td class=\"number\">$11533.89<\/td>\n<td class=\"number total\">$56810.64<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Sweden<\/td>\n<td class=\"number\">$13627.62<\/td>\n<td class=\"number\">$18234.86<\/td>\n<td class=\"number\">$8718.31<\/td>\n<td class=\"number\">$13914.35<\/td>\n<td class=\"number total\">$54495.14<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Canada<\/td>\n<td class=\"number\">$22746.94<\/td>\n<td class=\"number\">$7094.48<\/td>\n<td class=\"number\">$9225.70<\/td>\n<td class=\"number\">$11129.18<\/td>\n<td class=\"number total\">$50196.30<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Ireland<\/td>\n<td class=\"number\">$20500.54<\/td>\n<td class=\"number\">$8291.50<\/td>\n<td class=\"number\">$11376.50<\/td>\n<td class=\"number\">$9811.36<\/td>\n<td class=\"number total\">$49979.90<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Belgium<\/td>\n<td class=\"number\">$19410.67<\/td>\n<td class=\"number\">$3728.48<\/td>\n<td class=\"number\">$7740.70<\/td>\n<td class=\"number\">$2945.00<\/td>\n<td class=\"number total\">$33824.85<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Denmark<\/td>\n<td class=\"number\">$17049.90<\/td>\n<td class=\"number\">$2007.79<\/td>\n<td class=\"number\">$2127.25<\/td>\n<td class=\"number\">$11476.08<\/td>\n<td class=\"number total\">$32661.02<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Switzerland<\/td>\n<td class=\"number\">$4454.02<\/td>\n<td class=\"number\">$10928.70<\/td>\n<td class=\"number\">$7714.06<\/td>\n<td class=\"number\">$8595.88<\/td>\n<td class=\"number total\">$31692.66<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Mexico<\/td>\n<td class=\"number\">$3938.00<\/td>\n<td class=\"number\">$12432.71<\/td>\n<td class=\"number\">$4616.17<\/td>\n<td class=\"number\">$2595.20<\/td>\n<td class=\"number total\">$23582.08<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Finland<\/td>\n<td class=\"number\">$5532.80<\/td>\n<td class=\"number\">$5791.20<\/td>\n<td class=\"number\">$2884.41<\/td>\n<td class=\"number\">$4601.64<\/td>\n<td class=\"number total\">$18810.05<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Spain<\/td>\n<td class=\"number\">$7329.30<\/td>\n<td class=\"number\">$1875.80<\/td>\n<td class=\"number\">$4633.25<\/td>\n<td class=\"number\">$4144.85<\/td>\n<td class=\"number total\">$17983.20<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Italy<\/td>\n<td class=\"number\">$7082.09<\/td>\n<td class=\"number\">$2836.10<\/td>\n<td class=\"number\">$2484.37<\/td>\n<td class=\"number\">$3367.60<\/td>\n<td class=\"number total\">$15770.16<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Portugal<\/td>\n<td class=\"number\">$3335.79<\/td>\n<td class=\"number\">$4311.20<\/td>\n<td class=\"number\">$1519.24<\/td>\n<td class=\"number\">$2306.14<\/td>\n<td class=\"number total\">$11472.37<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Argentina<\/td>\n<td class=\"number\">$6684.10<\/td>\n<td class=\"number\">$716.50<\/td>\n<td class=\"number\">$0.00<\/td>\n<td class=\"number\">$718.50<\/td>\n<td class=\"number total\">$8119.10<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Norway<\/td>\n<td class=\"number\">$3354.40<\/td>\n<td class=\"number\">$822.35<\/td>\n<td class=\"number\">$500.00<\/td>\n<td class=\"number\">$1058.40<\/td>\n<td class=\"number total\">$5735.15<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Poland<\/td>\n<td class=\"number\">$587.50<\/td>\n<td class=\"number\">$1277.60<\/td>\n<td class=\"number\">$808.00<\/td>\n<td class=\"number\">$858.85<\/td>\n<td class=\"number total\">$3531.95<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead sumrow\">Sum<\/td>\n<td class=\"sum number\">$436780.50<\/td>\n<td class=\"sum number\">$285309.35<\/td>\n<td class=\"sum number\">$233666.36<\/td>\n<td class=\"sum number\">$310036.85<\/td>\n<td class=\"sum number total\">$1265793.06<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p class=\"clearfix\">&#8230;We need code like this (the first example in the body of the code was used to generate this Pivot-table)&#8230;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"><code>------------------------------------------------------------------------- \r\nCREATE PROCEDURE spDynamicHTMLCrossTab \r\n\r\n@RowValue VARCHAR(255), --what is the row header \r\n@ColValue VARCHAR(255), --what is the column header \r\n@Aggregate VARCHAR(255), --the aggregation value \r\n@FromExpression VARCHAR(8000), --the FROM, ON and WHERE clause \r\n@colOrderValue VARCHAR (255)=NULL, --how the columns are ordered \r\n@Title VARCHAR(80)='_', --the title to put in the first col of first row \r\n@RowSort VARCHAR(80)=NULL,--any special way the rows should be sorted \r\n@SortBy VARCHAR(255)='row asc', --what you sort the rows by (column heading) \r\n@UnitBefore VARCHAR(10)='',--the unit that each value has before (e.g. \u00c2\u00a3 or $) \r\n@UnitAfter VARCHAR(10)='',--The unit that each value has after e.g. % \r\n@ReturnTheDDL INT=0,--we return just the DLL \r\n@Debugging INT=0,--we look at the intermediate code \r\n@output VARCHAR(MAX) ='none' output, \r\n@style VARCHAR(MAX)='&lt;style type=\"text\/css\"&gt; \r\n\/*&lt;![CDATA[*\/ \r\n&lt;!-- \r\n#MyCrosstab { \r\nfont-family: Arial, Helvetica, sans-serif; font-size:small; \r\n} \r\n#MyCrosstab td{font-size:small; padding: 3px 10px 2px 10px; } \r\n#MyCrosstab td.number{ text-align: right; } \r\n#MyCrosstab td.rowhead{ border-right: 1px dotted #828282; font-weight: bold;} \r\n#MyCrosstab th{ font-size:small; border-bottom: 1px dotted #828282; text-align: center; } \r\n#MyCrosstab .sum{ border-top: 2px solid #828282; } \r\n#MyCrosstab .sumrow{ text-align: right } \r\n#MyCrosstab .total{ border-left: 1px solid #828282; } \r\n--&gt; \r\n\/*]]&gt;*\/ \r\n&lt;\/style&gt; \r\n' \r\n\r\n\/* \r\nDeclare @HTMLString varchar(max)  \r\nEXECUTE spDynamicHTMLCrossTab  \r\n    @RowValue='CompanyName', \r\n    @ColValue='datename(quarter,orderdate) \r\n     +case datepart(quarter,orderdate)  \r\n         when 1 then ''st''  \r\n         when 2 then ''nd''  \r\n         when 3 then ''rd''  \r\n         when 4 then ''th'' end', \r\n    @Aggregate= 'sum(subtotal)', \r\n    @FromExpression='FROM Orders  \r\n   INNER JOIN \"Order Subtotals\"  \r\n       ON Orders.OrderID = \"Order Subtotals\".OrderID \r\n  inner join customers on customers.customerID =orders.customerID', \r\n    @ColOrderValue='datepart(quarter,orderDate)', \r\n   @Unitbefore='$', \r\n   @sortby='total desc', \r\n   @Title ='value of orders per quarter', \r\n   @Output=@HTMLString output \r\nSelect @HTMLString \r\n\r\nExecute spDynamicHTMLCrossTab  \r\n    @RowValue='firstname+'' ''+lastname',  \r\n    @ColValue='DATENAME(year,orderDate)',  \r\n    @Aggregate= 'sum(subtotal)',  \r\n    @FromExpression='FROM Orders   \r\n   INNER JOIN \"Order Subtotals\"   \r\n       ON Orders.OrderID = \"Order Subtotals\".OrderID  \r\n   inner join employees on employees.EmployeeID =orders.EmployeeID',  \r\n    @ColOrderValue='datepart(year,orderDate)',  \r\n   @Unitbefore='$', \r\n   @sortby='total desc', \r\n   @Title ='Revenue per salesman per year ' \r\n\r\nExecute spDynamicHTMLCrossTab \r\n    @RowValue='firstname+'' ''+lastname', \r\n    @ColValue='Year(OrderDate)', \r\n    @Aggregate= 'count(*)', \r\n    @FromExpression='FROM Employees INNER JOIN Orders  \r\n    ON (Employees.EmployeeID=Orders.EmployeeID)', \r\n    @ColOrderValue='Year(OrderDate)', \r\n   @Title ='No. Sales per year', \r\n   @SortBy ='total desc', --what you sort the rows by (column heading) \r\n    @ReturnTheDDL =0, \r\n    @debugging=0 \r\n*\/ \r\nAS \r\nSET nocount ON \r\nDECLARE @Command NVARCHAR(MAX) \r\nDECLARE @DataRows VARCHAR(MAX) \r\nDECLARE @HeadingLines VARCHAR(8000) \r\n--make sure we have sensible defaults for orders \r\nSELECT @ColOrderValue=COALESCE(@ColOrderValue, @ColValue), \r\n    @rowsort=COALESCE(@RowSort,@RowValue), \r\n   @Sortby=COALESCE(@SortBy,@RowValue) \r\n--first construct tha SQL which is used to calculate the columns in a  \r\n--string \r\nDECLARE @StringTable TABLE \r\n  ( \r\n    MyID INT IDENTITY(1, 1), \r\n    string VARCHAR(8000), \r\n    waste numeric(19,8) \r\n  ) \r\n\r\nSELECT  \r\n\r\n   @Command='Select  \r\n @Headinglines=coalesce(@headinglines,''&lt;div id=\"MyCrosstab\"&gt; \r\n&lt;h3&gt;'+@title+'&lt;\/h3&gt; \r\n&lt;table cellpadding=\"0\" cellspacing=\"0\"&gt; \r\n&lt;thead&gt; \r\n&lt;tr class=\"header\"&gt;&lt;th&gt; &lt;\/th&gt;'')+''&lt;th&gt;'' \r\n+convert(varchar(100),' \r\n   +@ColValue+') +''&lt;\/th&gt;'', \r\n @DataRows=coalesce(@DataRows, \r\n''SELECT  \r\n[string]=''''&lt;tr&gt; \r\n  &lt;td class=\"rowhead'''' \r\n+ case when grouping(row)&lt;&gt;0 then'''' sumrow'''' else '''''''' end \r\n+''''\"&gt;''''+convert(varchar(100),case when row is null  \r\nthen ''''Sum'''' else [row] end)+''''&lt;\/td&gt; \r\n'') \r\n +''&lt;td class=\"'''' \r\n+ case when grouping(row)&lt;&gt;0 then''''sum'''' else '''''''' end \r\n+'''' number\"&gt;''''+'''''+@unitBefore+'''''+convert(varchar(100),sum( CASE col WHEN '''''' \r\n +convert(varchar(100),' \r\n   +@ColValue+') \r\n +'''''' THEN data else 0 END ))++'''''+@unitAfter+'''''+''''&lt;\/td&gt; \r\n''  '+@FromExpression+' \r\nGROUP BY '+@ColValue+' \r\norder by max('+@ColorderValue+')' \r\n--Now we execute the string to obtain the SQL that we will use for the \r\n--crosstab query \r\nEXECUTE sp_ExecuteSQL @command,N'@DataRows VARCHAR(MAX) OUTPUT, \r\n  @Headinglines VARCHAR(MAX) OUTPUT', @DataRows output,@Headinglines OUTPUT \r\n  IF @@error &gt; 0 --display the string if there is an error \r\n    BEGIN \r\n      RAISERROR ( 'offending first-phase code was ...%s', 0, 1, @command ) \r\n      RETURN 1 \r\n    END \r\nIF @Debugging &lt;&gt;0 SELECT @Command \r\n\r\nINSERT INTO  @StringTable(string) SELECT @Style \r\nINSERT INTO  @StringTable(string) SELECT @Headinglines+'&lt;th&gt;Total&lt;\/th&gt;&lt;\/tr&gt; \r\n   &lt;\/thead&gt; \r\n   &lt;tbody&gt;' \r\nSELECT @DataRows= \r\n@DataRows+'&lt;td class=\"'' \r\n  + case when grouping(row)&lt;&gt;0 then''sum'' else '''' end+'' number total\"&gt;'' \r\n  +'''+@unitBefore+'''+convert(varchar(100),sum( data ))+'''+@unitAfter \r\n  +'''+''&lt;\/td&gt;&lt;\/tr&gt;'', [total]=convert(numeric(19,8),sum( data )) \r\nfrom  \r\n   (select [row]='+@RowValue+',  \r\n           [col]='+@ColValue+',  \r\n           [data]='+@Aggregate+', \r\n           [sort]=max('+@rowsort+') \r\n '+@FromExpression+'  \r\n    GROUP BY '+@RowValue+', '+@ColValue+' \r\n)f \r\ngroup by row with rollup \r\norder by grouping(row),'+@Sortby \r\n--and execute it \r\nIF @ReturnTheDDL&lt;&gt;0 SELECT @DataRows ELSE \r\n   INSERT INTO  @StringTable(string,waste) \r\n       EXECUTE (@DataRows) \r\n  IF @@error &gt; 0  \r\n    BEGIN \r\n      RAISERROR ( 'offending second-phase code was ...%s', 0, 1, @DataRows ) \r\n      RETURN 1 \r\n    END \r\nINSERT INTO  @StringTable(string) SELECT '&lt;\/tbody&gt;&lt;\/table&gt;&lt;\/div&gt;' \r\n\r\nIF @Output='none'  \r\n    SELECT string FROM @StringTable ORDER BY MyID \r\nELSE  \r\n    SELECT @Output=COALESCE(@Output,'')+ string  \r\n       FROM @StringTable  \r\n       ORDER BY MyID<\/code><\/pre>\n<p>(n.b this is the SQL Server 2005 version. The SQL Server 2000 version is included with the files you can download at the bottom of the article)<\/p>\n<p>You will have noticed a few things here.<\/p>\n<ul>\n<li>The inputs are the same as the first stored procedure, spDynamicCrossTab. This means that you can try out your parameters in SSMS until you have things the way you want them and then you can just move to spDynamicHTMLCrossTab to concentrate on getting the presentation aspects as you want.<\/li>\n<li>We have separated the style from the code. All presentation is in an inline style block. This means you can change the way the crosstab looks to your heart&#8217;s content.<\/li>\n<li>with both stored procedures, you can specify the order of both the columns and rows precisely (it is always nice to have the months, or the days of the week in the correct order!<\/li>\n<li>you can specify the units, either before(e.g. &#8216;\u00c2\u00a3&#8217; or &#8216;$&#8217;) or after (e.g &#8216;%&#8217;) the aggregate values<\/li>\n<li>We provide you with an optional output variable so you can take the results and save it easily to a file, using the technique Phil described in his Blog <a href=\"http:\/\/www.simple-talk.com\/community\/blogs\/philfactor\/archive\/2007\/07\/13\/33494.aspx\">&#8216;Using BCP to export the contents of MAX datatypes to a file&#8217;<\/a><\/li>\n<\/ul>\n<p>You can change the appearance of the crosstab simply by changing the inline style. For example, this &#8230;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"><code>EXECUTE spDynamicHTMLCrossTab \r\n    @RowValue='firstname+'' ''+lastname', \r\n    @ColValue='Year(OrderDate)', \r\n    @Aggregate= 'count(*)', \r\n    @FromExpression='FROM Employees INNER JOIN Orders  \r\n    ON (Employees.EmployeeID=Orders.EmployeeID)', \r\n    @ColOrderValue='Year(OrderDate)', \r\n   @Title ='No. Sales per year', \r\n   @SortBy ='total desc', --what you sort the rows by (column heading) \r\n   @Style='&lt;style type=\"text\/css\"&gt; \r\n\/*&lt;![CDATA[*\/ \r\n&lt;!-- \r\n#MyCrosstab { \r\nfont-family: \"Times New Roman\", Times, serif; font-size:small; \r\n} \r\n#MyCrosstab td{font-size:small; padding: 3px 10px 2px 10px; } \r\n#MyCrosstab td.number{ text-align: right; } \r\n#MyCrosstab td.rowhead{ background-color: #C5DC9C; font-weight: bold;} \r\n#MyCrosstab th{ background-color: #C5DC9C; font-size: small;  border-bottom: text-align: center; } \r\n#MyCrosstab .sum{ border-top: 3px double #828282; } \r\n#MyCrosstab .sumrow{ border-top: 1px solid #828282; text-align: right } \r\n#MyCrosstab .total{ border-left: 1px solid #828282; } \r\n--&gt; \r\n\/*]]&gt;*\/ \r\n&lt;\/style&gt;' <\/code><\/pre>\n<p>&#8230; will give you this<\/p>\n<p><style type=\"text\/css\">\n#yetanothercrosstab {\nfont-family: \"Times New Roman\", Times, serif; font-size:small;\n}\n#yetanothercrosstab td{font-size:small; padding: 3px 10px 2px 10px; }\n#yetanothercrosstab td.number{ text-align: right; }\n#yetanothercrosstab td.rowhead{ background-color: #C5DC9C; font-weight: bold;}\n#yetanothercrosstab th{ background-color: #C5DC9C; font-size: small;  border-bottom: text-align: center; }\n#yetanothercrosstab .sum{ border-top: 3px double #828282; }\n#yetanothercrosstab .sumrow{ border-top: 1px solid #828282; text-align: right }\n#yetanothercrosstab .total{ border-left: 1px solid #828282; }\n<\/style>\n<\/p>\n<div id=\"yetanothercrosstab\">\n<h3>No. Sales per year<\/h3>\n<table>\n<thead>\n<tr class=\"header\">\n<th>\u00a0<\/th>\n<th>1996<\/th>\n<th>1997<\/th>\n<th>1998<\/th>\n<th>Total<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td class=\"rowhead\">Margaret Peacock<\/td>\n<td class=\"number\">31<\/td>\n<td class=\"number\">81<\/td>\n<td class=\"number\">44<\/td>\n<td class=\"number total\">156<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Janet Leverling<\/td>\n<td class=\"number\">18<\/td>\n<td class=\"number\">71<\/td>\n<td class=\"number\">38<\/td>\n<td class=\"number total\">127<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Nancy Davolio<\/td>\n<td class=\"number\">26<\/td>\n<td class=\"number\">55<\/td>\n<td class=\"number\">42<\/td>\n<td class=\"number total\">123<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Laura Callahan<\/td>\n<td class=\"number\">19<\/td>\n<td class=\"number\">54<\/td>\n<td class=\"number\">31<\/td>\n<td class=\"number total\">104<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Andrew Fuller<\/td>\n<td class=\"number\">16<\/td>\n<td class=\"number\">41<\/td>\n<td class=\"number\">39<\/td>\n<td class=\"number total\">96<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Robert King<\/td>\n<td class=\"number\">11<\/td>\n<td class=\"number\">36<\/td>\n<td class=\"number\">25<\/td>\n<td class=\"number total\">72<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Michael Suyama<\/td>\n<td class=\"number\">15<\/td>\n<td class=\"number\">33<\/td>\n<td class=\"number\">19<\/td>\n<td class=\"number total\">67<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Anne Dodsworth<\/td>\n<td class=\"number\">5<\/td>\n<td class=\"number\">19<\/td>\n<td class=\"number\">19<\/td>\n<td class=\"number total\">43<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead\">Steven Buchanan<\/td>\n<td class=\"number\">11<\/td>\n<td class=\"number\">18<\/td>\n<td class=\"number\">13<\/td>\n<td class=\"number total\">42<\/td>\n<\/tr>\n<tr>\n<td class=\"rowhead sumrow\">Sum<\/td>\n<td class=\"sum number\">152<\/td>\n<td class=\"sum number\">408<\/td>\n<td class=\"sum number\">270<\/td>\n<td class=\"sum number total\">830<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>Lastly, although there is a lot more one can say about these procedures and the tricks one can use, especially with the CSS, here is an illustration of the way one might save the results of your crosstab to an HTML file for your management reporting intranet site. This uses Phil&#8217;s technique taken from his blog entry <a href=\"http:\/\/www.simple-talk.com\/community\/blogs\/philfactor\/archive\/2007\/07\/13\/33494.aspx\">Using BCP to export the contents of MAX datatypes to a file<\/a> (Phil: Thanks for the plug, Robyn!)<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"><code>\r\nDECLARE @HTMLString VARCHAR(MAX)  \r\nEXECUTE spDynamicHTMLCrossTab \r\n    @colValue='DATENAME(year,orderDate)', \r\n    @rowValue='DATENAME(month,orderDate)', \r\n    @Aggregate= 'sum(subtotal)', \r\n   @Rowsort='DATEpart(month,orderDate)', \r\n    @FromExpression='FROM Orders  \r\n   INNER JOIN \"Order Subtotals\"  \r\n       ON Orders.OrderID = \"Order Subtotals\".OrderID \r\n   inner join employees on employees.EmployeeID =orders.EmployeeID', \r\n    @ColOrderValue='datepart(year,orderDate)', \r\n   @Title ='Customers orders per month ', \r\n   @sortby='max(sort) asc', \r\n   @Output=@HTMLString output \r\nSELECT @HTMLString= \r\n'&lt;!DOCTYPE HTML PUBLIC \"-\/\/W3C\/\/DTD HTML 4.01 Transitional\/\/EN\"  \r\n                           \"http:\/\/www.w3.org\/TR\/html4\/loose.dtd\"&gt; \r\n&lt;html&gt; \r\n&lt;head&gt;&lt;title&gt;Customers orders per month&lt;\/title&gt;&lt;\/head&gt; \r\n&lt;body&gt;'+@HTMLString+'&lt;\/body&gt;' \r\nEXECUTE spSaveTextToFile @HTMLString, 'C:\\MyHTMLReport.html'<\/code><\/pre>\n<p>So that&#8217;s it. We&#8217;ve enjoyed ourselves trying things out, and we&#8217;ve been surprised how far we can take the dynamic creation of pivot tables. We suggest you take the workshop and explore the ideas. If you discover anything interesting, we&#8217;d love to hear your comments! We&#8217;d particularly like to hear of interesting CSS layouts, though displaying them on Simple-Talk will be very difficult.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Robyn and Phil turn their attention to the bedrock of management reporting, the Pivot Table. Under Phil&#8217;s &#8216;wild man&#8217; influence, they end up with some rather radical ideas.&hellip;<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4807,4150,4252,4460],"coauthors":[6813,6814],"class_list":["post-286","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-crosstab-pivot-table-tsql-html","tag-sql","tag-t-sql-programming","tag-workbench"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/286","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=286"}],"version-history":[{"count":18,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/286\/revisions"}],"predecessor-version":[{"id":74376,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/286\/revisions\/74376"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=286"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=286"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=286"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=286"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}