asked on 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?
Microsoft SQL ServerSQL
Last Comment
Sharath S
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
An excellent article from our expert..
https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html