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

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?
Sam OZAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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 GuideCommented:
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 OZAuthor 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 GuideCommented:
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

SharathData EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.