Convert rows into multiple columns

Question

I have result like this

SubjectId   ReasonId    TypeId

1           1           0
1           1           0
1           2           0
1           2           0
1           3           0
1           4           1
1           4           1
1           6           1
1           6           1
2           1           0
...
...
...

But, I have to create report like following:

SubjectId   ReasonId(Type0)   Count(Type0)   ReasonId(Type1)   Count(Type1)

1           1                 2              4                 2
1           2                 2              6                 2
1           3                 1              5                 0
2           1                 1              4                 0
2           2                 0              5                 0
2           3                 0              6                 0

Let me explain:

Instead of showing result in standard view, where each record is new row, I must break result into two groups:

First group was fixed three rows per SubjectId with value of TypeId = 0, and for each Reason (1, 2, 3) I must show count of rows for specific reason. ReasonId must be sorted in descending order by count of rows.

Second group is located right of first group, and story is same, instead of TypeId = 0 there is TypeId = 1. Same descending order is needed.

So, every SubjectId has exactly three rows for every ReasonId (for Type 0 there are reasons 1, 2, 3 and for Type 1 there are reasons 4, 5, 6).

If some subject has no rows for any reasons, I must write 0.

i refer this, http://stackoverflow.com/questions/20632582/pivot-a-table-on-a-value-but-group-the-data-on-one-line-by-another/20633609#20633609 — KumarHarsh, Dec 17 '13 at 12:02

Anon · Answer 1 · 2013-12-18T17:07:34.097

So, every SubjectId has exactly three rows for every ReasonId (for Type 0 there are reasons 1, 2, 3 and for Type 1 there are reasons 4, 5, 6).

This is the key information that the db needs. Encode these rules into a table and cross join it to your data table.

    SELECT
      [SubjectId],
      [ReasonID(Type0)],
      SUM(CASE WHEN [ReasonId] = [ReasonID(Type0)] THEN 1 ELSE 0 END) AS [Count(Type0)],
      [ReasonID(Type1)],
      SUM(CASE WHEN [ReasonId] = [ReasonID(Type1)] THEN 1 ELSE 0 END) AS [Count(Type1)]
    FROM MyTable S
    CROSS JOIN (VALUES (1,4),(2,5),(3,6)) X([ReasonID(Type0)],[ReasonID(Type1)])
    GROUP BY [SubjectId],[ReasonID(Type0)],[ReasonID(Type1)]

To sort each reason independently:

WITH SortedCounts AS (
  SELECT
    S.[SubjectId],T.[TypeId],T.[ReasonId],
    SUM(CASE WHEN S.[ReasonId] = T.[ReasonId] AND S.[TypeId] = T.[TypeId] THEN 1 ELSE 0 END) AS [Count],
    ROW_NUMBER() OVER(
      PARTITION BY S.[SubjectId],T.[TypeId]
      ORDER BY SUM(CASE WHEN S.[ReasonId] = T.[ReasonId] AND S.[TypeId] = T.[TypeId] THEN 1 ELSE 0 END) DESC, T.[ReasonId] ASC
    ) [SortOrder]
  FROM MyTable S
  CROSS JOIN (VALUES(0,1),(0,2),(0,3),(1,4),(1,5),(1,6)) T([TypeId],[ReasonId])
  GROUP BY S.[SubjectId],T.[TypeId],T.[ReasonId]
)
SELECT
  [SubjectId],
  MIN(CASE WHEN [TypeId] = 0 THEN [ReasonId] END) AS [ReasonId(Type0)],
  MIN(CASE WHEN [TypeId] = 0 THEN [Count]    END) AS [Count(Type0)],
  MIN(CASE WHEN [TypeId] = 1 THEN [ReasonId] END) AS [ReasonId(Type1)],
  MIN(CASE WHEN [TypeId] = 1 THEN [Count]    END) AS [Count(Type1)]
FROM SortedCounts T
GROUP BY [SubjectId],[SortOrder]
ORDER BY [SubjectId],[SortOrder]

This is ok for case that pairs are always (1,4), (2, 5), (3, 6), but as I stated, pairs must be made by count, not by ID. Example: somewhere (1, 4), but somewhere (1, 5) or (1, 6), and so on... — veljasije, Dec 18 '13 at 07:15
Then you need to add a ROW_NUMBER() to the counts, and then wrap it in a CTE so you can group and sort by it. — Anon, Dec 18 '13 at 17:09

Convert rows into multiple columns

1 Answers1