We help IT Professionals succeed at work.

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

59 Views
Last Modified: 2018-12-11
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

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

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

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
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

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
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.