A Possible Alternative to SQL UNPIVOT

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  January 28, 2017 at 7:58 am

  #1925481

  alessandro mortola - Saturday, January 28, 2017 2:56 AM

  Very interesting article. In my opinion there could be a little problem in case you deal with a query containing one or more joins. In this case the resultant XML is not as flat as the one of the example. Look at the following code, for example:
  
  CREATE TABLE A1 (Id INT, Agent varchar(20))
  CREATE TABLE R1 (AgentId INT, Revenue2014 INT,  Revenue2015 INT, Revenue2016 INT)

  INSERT INTO A1 VALUES
  (1, 'John'),
  (2, 'Paul'),
  (3, 'Alex')

  INSERT INTO R1 VALUES
  (1, 100, 200, 250),
  (2, 120, 80, 95),
  (3, 50, 70, 150)

  SELECT Agent, Revenue2014, Revenue2015, Revenue2016
  FROM A1
  INNER JOIN R1 ON A1.Id = R1.AgentId
  FOR XML AUTO, ELEMENTS, TYPE

  The XML would be something like that, and the parsing operation would be more difficult.
  
<A1>
  <Agent>John</Agent>
  <R1>
    <Revenue2014>100</Revenue2014>
    <Revenue2015>200</Revenue2015>
    <Revenue2016>250</Revenue2016>
  </R1>
</A1>
<A1>
  <Agent>Paul</Agent>
  <R1>
    <Revenue2014>120</Revenue2014>
    <Revenue2015>80</Revenue2015>
    <Revenue2016>95</Revenue2016>
  </R1>
</A1>
<A1>
  <Agent>Alex</Agent>
  <R1>
    <Revenue2014>50</Revenue2014>
    <Revenue2015>70</Revenue2015>
    <Revenue2016>150</Revenue2016>
  </R1>
</A1>


  The use of a CTE does not help. One possible workaround is to put the result of the query into a temporary table and then select from it.
  Any other ideas?
  

  Simply don't use FOR XML AUTO, ELEMENTS, TYPE but rather PATH, here is a quick example
  😎
  SELECT
  X1.Agent   AS AGENT
 ,Y1.Revenue2014 AS Revenue2014
 ,Y1.Revenue2015 AS Revenue2015
 ,Y1.Revenue2016 AS Revenue2016
FROM   A1  X1
INNER JOIN R1  Y1
ON    X1.Id = Y1.AgentId
FOR XML PATH (''), ROOT('X'),TYPE

  The XML output
  <X>
<AGENT>John</AGENT>
<Revenue2014>100</Revenue2014>
<Revenue2015>200</Revenue2015>
<Revenue2016>250</Revenue2016>
<AGENT>Paul</AGENT>
<Revenue2014>120</Revenue2014>
<Revenue2015>80</Revenue2015>
<Revenue2016>95</Revenue2016>
<AGENT>Alex</AGENT>
<Revenue2014>50</Revenue2014>
<Revenue2015>70</Revenue2015>
<Revenue2016>150</Revenue2016>
</X>

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  January 28, 2017 at 8:08 am

  #1925483

  Just a word of warning, the XML methods posted on this thread so far are naive and ridiculously expensive, at least use the text() function to avoid the XML reconstruction for the output.
  😎

  Quick PoC
  USE TEEST;
GO
SET NOCOUNT ON;

  --/* Uncomment this line to skip the test set creation
--===== If the test table already exists, drop it to make reruns in SSMS easier.
 IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
  DROP TABLE #TestTable
;
--===== Create a test table with a PK in place
CREATE TABLE #TestTable
  (
 RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
  ,ColA INT
  ,ColB INT
  ,ColC INT
  )
;
--===== Populate the table using minimal logging if not in FULL Recovery Model
INSERT INTO #TestTable WITH (TABLOCK)
  (ColA, ColB, ColC)
SELECT TOP 100000
 ColA = ABS(CHECKSUM(NEWID())%1000)+1
  ,ColB = ABS(CHECKSUM(NEWID())%1000)+1
  ,ColC = ABS(CHECKSUM(NEWID())%1000)+1
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
-- */

  DECLARE @INT_BUCKET INT = 0;
DECLARE @CHAR_BUCKET VARCHAR(20) = '';
DECLARE @timer TABLE
(
  T_TXT   VARCHAR(50)  NOT NULL
 ,T_TS   DATETIME2(7)  NOT NULL DEFAULT (SYSDATETIME())
 ,T_CPU_BUSY INT     NOT NULL DEFAULT (@@CPU_BUSY)
 ,T_IO_BUSY INT     NOT NULL DEFAULT (@@IO_BUSY)
 ,T_IDLE  INT     NOT NULL DEFAULT (@@IDLE)
);

  INSERT INTO @timer(T_TXT) VALUES('ORIGINAL');
DECLARE @NCHAR_BUCKET NVARCHAR(128) = N'';
WITH TableWithXmlCol AS
(
SELECT RowNum,
(SELECT
ColA,
ColB,
ColC
FROM #TestTable P
WHERE P2.RowNum = P.RowNum
FOR XML AUTO, ELEMENTS, TYPE
) AS XmlCol
FROM #TestTable P2
)
SELECT
  @INT_BUCKET = TableWithXmlCol.RowNum,
  @NCHAR_BUCKET = T.c.value('fn:local-name(.)', 'sysname'),
  @CHAR_BUCKET = T.c.value('.', 'varchar(20)')
FROM TableWithXmlCol
CROSS APPLY TableWithXmlCol.XmlCol.nodes('//P/*') T(c);
INSERT INTO @timer(T_TXT) VALUES('ORIGINAL');

  INSERT INTO @timer(T_TXT) VALUES('text()');
WITH TableWithXmlCol AS
(
SELECT RowNum,
(SELECT
ColA,
ColB,
ColC
FROM #TestTable P
WHERE P2.RowNum = P.RowNum
FOR XML AUTO, ELEMENTS, TYPE
) AS XmlCol
FROM #TestTable P2
)
SELECT
@INT_BUCKET = TableWithXmlCol.RowNum,
@NCHAR_BUCKET = T.c.value('fn:local-name(.)', 'sysname'),
@CHAR_BUCKET = T.c.value('(./text())[1]', 'varchar(20)')
FROM TableWithXmlCol
CROSS APPLY TableWithXmlCol.XmlCol.nodes('//P/*') T(c)
INSERT INTO @timer(T_TXT) VALUES('text()');

  SELECT
  T.T_TXT
 ,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
 ,MAX(T.T_CPU_BUSY) - MIN(T.T_CPU_BUSY)    AS CPU_BUSY
 ,MAX(T.T_IO_BUSY ) - MIN(T.T_IO_BUSY )    AS IO_BUSY
 ,MAX(T.T_IDLE  ) - MIN(T.T_IDLE  )    AS IDLE
FROM  @timer T
GROUP BY T.T_TXT
ORDER BY DURATION;

  Timer's results (avg of 4)
  

• T_TXT       DURATION    CPU_BUSY    IO_BUSY     IDLE
----------- ----------- ----------- ----------- -----------
text()      8679480     272         0           812
ORIGINAL    12066915    446         27          1035

?