How to represent rows as column from details table ?

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  January 6, 2021 at 3:42 am

  #3827932

  I work on sql server 2012 i face issue ican't represent rows as column .

  i need to represent code type from details table to display as column with

  values of countparts

  join between master zplid and details zplid is zplid

  every zplid have group of code type so instead of represent code type as rows

  i will represent it as columns

  columns will have header as code type and content column will be countparts

  so How to do that please ?

  create table #zplidmaster
   (
   zplid int,
   zplidname  nvarchar(50)
   )
   insert into #zplidmaster(zplid,zplidname) 
   values
   (4124,'tetanium'),
   (4125,'FilmCapacitor'),
   (4145,'CeramicCapacitor'),
   (4170,'Holetransistor'),
   (4190,'resistor')
   --drop table #zpliddetails
   create table #zpliddetails
   (
   zplid int,
   CodeType  int,
   CountParts int
   )
   insert into #zpliddetails(zplid,CodeType,CountParts) 
   values
   (4124,9089,9011),
   (4124,7498,7000),
   (4125,9089,2000),
   (4125,7498,1000),
   (4145,9089,3000),
   (4145,7498,8500),
   (4170,9089,7600),
   (4170,7498,6600),
   (4190,9089,9001),
   (4190,7498,9003)
  

  

• DesNorton

  SSC-Insane

  Points: 24277

  More actions

  January 6, 2021 at 6:17 am

  #3827952

  What you need is a PIVOT or a Cross-Tab.  The following SQL will work for your provided sample data

  SELECT      m.zplid
            , m.zplidname
            , [9089] = MAX( CASE WHEN d.CodeType = 9089 THEN CountParts END )
            , [7498] = MAX( CASE WHEN d.CodeType = 7498 THEN CountParts END )
  FROM        #zplidmaster  AS m
  INNER JOIN  #zpliddetails AS d ON m.zplid = d.zplid
  GROUP BY    m.zplid, m.zplidname;
  

  I would suggest reading the following articles by Jeff Moden in order to get an understanding of how/why the code works

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

  ---

  How to post data/code on a forum to get the best help.

• January 8, 2021 at 3:31 am

  This was removed by the editor as SPAM

• April 27, 2021 at 7:04 am

  This was removed by the editor as SPAM