How to represent rows as column from details table ?

Question

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)  

expected result

zplid	zplidname	9089	7498  
4124	tetanium	9011	7000  
4125	FilmCapacitor	2000	1000  
4145	CeramicCapacitor3000	8500  
4170	Holetransistor	7600	6600  
4190	resistor	9001	9003  

Answer 1

> I face issue ican't represent rows as column .<<

Yes, that's exactly right! Rows and columns are totally, completely, different! A row represents an entity which is an element in a set (table) and it is identified by a key (a subset of the columns which is unique). A column in a properly designed table will be a scalar data value that models an attribute of the entity modeled by the row.

> I need to represent code type from details table to display as column with values of countparts <<

Please read ISO 11179 standards. An attribute can be either a "<something>_code" or a <something>_type, but not this weird hybrid. These post fixes are what we call "attribute properties" and an attribute can have one and only one of them. Please take a course on basic data modeling! You're doing everything completely wrong

You are still trying to write SQL as if you were in a procedural language, with a file system and link list. SQL is a declarative, relational, language that is said oriented

I do not wish to post several thousand words again, so please Google "nested set model" and redesign your schema into a real relational database.

Answer 2

If you only need such output, TSQL can be implemented, but if you have a lot of data and need to match your description, it is not easy to implement.Do you really need such an output？

Echo

Answer 3

;WITH CTE_9089 AS (
    SELECT zplid,CodeType,CountParts
    FROM #zpliddetails
    WHERE CodeType = 9089
),
CTE_7498 AS (
    SELECT zplid,CodeType,CountParts
    FROM #zpliddetails
    WHERE CodeType = 7498
)

SELECT t1.zplid, t1.zplidname, c1.CountParts AS [9089], c2.CountParts AS [7498]
FROM #zplidmaster AS t1
INNER JOIN CTE_9089 AS c1 ON c1.zplid = t1.zplid
INNER JOIN CTE_7498 AS c2 ON c2.zplid = t1.zplid;

Answer 4

This is called pivotting or cross tabbing
You can implement it in multiple ways

Like

--method 1 (classical crosstab)

 select m.zplid,m.zplidname,
 max(case when d.CodeType = 9089 then countparts end) as [9089],
  max(case when d.CodeType = 7498 then countparts end) as [7498]
 from #zplidmaster m
 inner join #zpliddetails d
 on d.zplid = m.zplid
 group by m.zplid,m.zplidname


 --method 2 (PIVOT operator)
 select *
 from
 (
 select m.zplid,m.zplidname,d.CodeType,d.countparts
 from #zplidmaster m
 inner join #zpliddetails d
 on d.zplid = m.zplid
 )t
pivot(max(countparts) for codetype in ([9089],[7498]))p

To make them dynamic see

script-to-create-dynamic-pivot-queries-in-sql-server