troubleshooting Question

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

Avatar of Sam OZ
Sam OZFlag for Australia asked on
Microsoft SQL ServerSQL
5 Comments2 Solutions61 ViewsLast Modified:
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

    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?
Sharath S
Data Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros