pivot with group by

• tanehome

  Ten Centuries

  Points: 1165

  More actions

  November 15, 2018 at 2:41 am

  #368947

  
  Hi All,
  I need your assist in a simple query to pivot the below data and group them by docid.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  November 15, 2018 at 7:34 am

  #2013652

  tanehome - Thursday, November 15, 2018 2:41 AM

  
  Hi All,
  I need your assist in a simple query to pivot the below data and group them by docid.

  ;WITH SampleData AS (
   SELECT *
   FROM (VALUES
    ('doc1', 1, 'a'),
    ('doc1', 2, 'b')
   ) d (docid, ver, code)
  )
  SELECT
   docid,
   [1] = MAX(CASE WHEN ver = 1 THEN code END),
   [2] = MAX(CASE WHEN ver = 2 THEN code END)
  FROM SampleData
  GROUP BY docid

  For more info see this article.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  November 15, 2018 at 8:23 am

  #2013672

  tanehome - Thursday, November 15, 2018 2:41 AM

  
  Hi All,
  I need your assist in a simple query to pivot the below data and group them by docid.

  Chris's cross tab query is very good. To know that different solution exist try below one

  
;WITH SampleData AS (
SELECT *
FROM (VALUES
('doc1', 1, 'a'),
('doc1', 2, 'b')
) d (docid, ver, code)
)
select * from
(SELECT
*
FROM SampleData) da
pivot
(
--min(ver) for code in (a,b)
min(code) for ver in ([1],[2])
)as dat

  Saravanan