I have an sql server 2016 table TB_Docs with fields
DocUID, DocID , DocName, …..
I have TB_DocAttr with following fields
UID, OBJID , PropertyVal , StrValue
( OBJID in TB-DocAttr is a foreign key to primary key DocUID in TB_Docs)
sample TB_Docs
DocUID DocID DocName
123 UID_Doc1 Doc1
345 UID_Doc2 Doc2
678 UID_Doc3 Doc3
TB_DocAttr
UID OBJID PropertyVal StrVal
111 123 Prop1 Val11
222 123 Prop2 Val12
333 123 Prop3 Val13
444 345 Prop2 Val22
555 345 Prop3 Val23
I need a query that can give the properties and values in the same row like this
DocName DocUID Prop1 Prop2 Prop3
Doc1 123 Val11 Val12 Val13
Doc2 345 Val22 Val23
In Oracle I could get the "flattening" of the properties with
MAX(DECODE(p.propertyval,'Prop1',p.strvalue)) AS Prop1,
MAX(DECODE(p.propertyval,'Prop2',p.strvalue)) AS Prop2
How can I get it in Sql Server?
An excellent article from our expert..
https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html