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,'
MAX(DECODE(p.propertyval,'
How can I get it in Sql Server?
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,'
MAX(DECODE(p.propertyval,'
How can I get it in Sql Server?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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
An excellent article from our expert..
https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html
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
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
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..
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
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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.