MS SQl Create columns based on values in rows

Question

I have a table:

   --ID----ID2----Error
      1     1      A
      1     2      B
      1     3      A

What i want:

 ---ID---ErrorTypeA---ErrorTypeB---
    1         2            1

I don't know how many ErrorType there is, so they should be created dynamically based on the distinct values in Error column....Is that possible?

yes, that is called "pivot table": https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Ricardo Pontual, May 30 '18 at 12:09
Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Ricardo Pontual, May 30 '18 at 12:09

score 0 · Answer 1 · answered May 30 '18 at 12:39

Try this Pivot

DECLARE @Temp AS TABLE (ID INT,ID2 INT,Error CHAR(2))
INSERT INTO @Temp
SELECT 1,1,'A' UNION ALL
SELECT 1,2,'B' UNION ALL
SELECT 1,3,'A' 

SELECT  Id,
        MAX([ErrorTypeA]) AS [ErrorTypeA],
        MAX([ErrorTypeB]) AS [ErrorTypeB]
FROM
(
SELECT *, 'ErrorType'+Error As ErrorCol FROM @Temp
) AS SRc
PIVOT
(
COUNT(ID2) FOR ErrorCol IN ([ErrorTypeA],[ErrorTypeB] )
)
AS PVT
GROUP BY Id

Result

Id  ErrorTypeA  ErrorTypeB
--------------------------
1       2           1

Demo :http://rextester.com/TYYX56571

neer · Answer 2 · 2018-06-01T06:59:38.243

You have to do it with dynamic query. it is first necessary to prepare the result set.

CREATE TABLE #Tbl (ID INT, ID2 INT, ERROR VARCHAR)
INSERT INTO #Tbl
VALUES
(1, 1, 'A'),
(1, 2, 'B'),
(1, 3, 'A')

DECLARE @ErrorTags NVARCHAR(MAX) = ''
SELECT @ErrorTags += CONCAT(',[', ERROR, ']') FROM  #Tbl GROUP BY ERROR

DECLARE @Query AS  NVARCHAR(MAX) = '
SELECT *
FROM 
(   
    SELECT 
        ID,
        ERROR,
        COUNT(1) CountOfError
    FROM 
        #Tbl
    GROUP BY ID,ERROR
) SRC
PIVOT (
  AVG(CountOfError)
  FOR ERROR IN ('+ STUFF(@ErrorTags, 1, 1, '') +')
) PIV '

execute sp_executesql @Query

Result:

ID          A           B
----------- ----------- -----------
1           2           1

MS SQl Create columns based on values in rows

2 Answers2