Sql Server query to get the values from multiple rows in a single row

Sam OZ
Sam OZ used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Simple, PIVOT would do the trick..
here you go.
SELECT  *
FROM (
select DocUID, DocID, DocName, StrVal, PropertyVal
from tb_docs d
join TB_DocAttr da on d.DocUID = da.OBJID   ) p
PIVOT
(MAX(StrVal) FOR PropertyVal IN ([Prop1], [Prop2], [Prop3])) pvt

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
If the PIVOT values Prop1, Prop2 and so on are dynamic in nature, then you might need to use something like this..
An excellent article from our expert..
https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html
Sam OZ

Author

Commented:
The Pivot values are not dynamic . I have only fixed set of values
But I get an error  "PropertyVal is invalid …  not in aggregate function "   .  Should there be a group by in pvt  ?

I hope the prop1, prop2 should not have the single quotes (like 'prop1' ) as given by you
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
FYI, above code works fine for me with the sample data you have shared out..
kindly let me know whether you have modified the query and if so, then share the same..
create table TB_Docs(DocUID int, DocID varchar(20), DocName varchar(20))
insert into TB_Docs values (123, 'UID_Doc1', 'Doc1')
, (345, 'UID_Doc2', 'Doc2')
, (678, 'UID_Doc3', 'Doc3')

create table TB_DocAttr(UID int, OBJID int, PropertyVal varchar(20), StrVal varchar(20))
INSERT INTO TB_DocAttr values (111, 123, 'Prop1', 'Val11')
, (222, 123, 'Prop2', 'Val12')
, (333, 123, 'Prop3', 'Val13')
, (444, 345, 'Prop2', 'Val22')
, (555, 345, 'Prop3', 'Val23')

SELECT  *
FROM (
select DocUID, DocID, DocName, StrVal, PropertyVal
from tb_docs d
join TB_DocAttr da on d.DocUID = da.OBJID   ) p
PIVOT
(MAX(StrVal) FOR PropertyVal IN ([Prop1], [Prop2], [Prop3])) pvt

Open in new window

Data Engineer
Commented:
you can try like this also.

SELECT d.DocUID, d.DocID, d.DocName, da.Prop1, da.Prop2, da.Prop3
  FROM TB_Docs d
  JOIN (
        SELECT OBJID
               ,MAX(CASE PropertyVal WHEN 'Prop1' THEN StrVal END) Prop1
               ,MAX(CASE PropertyVal WHEN 'Prop2' THEN StrVal END) Prop2
               ,MAX(CASE PropertyVal WHEN 'Prop3' THEN StrVal END) Prop3
          FROM TB_DocAttr
         GROUP BY OBJID) da
    ON d.DocUID = da.OBJID

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial