pivot data from header and detail with code from other table

Question

pivot data from header and detail with code from other table

Shanmuga Raj

Ten Centuries

Points: 1178
More actions
February 3, 2014 at 7:42 am

#287889

I need to display data from both header and detail, details has to come from other table also
--CREATE TABLE
-----------------
CREATE TABLE [dbo].[Table_Dept](
[DeptId] [int] NULL,
[DeptCode] [char](2) NULL,
[DeptName] [nvarchar](100) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Table_Detail](
[Header_ID] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[DescValue] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[Table_Header](
[Header_Id] [int] NULL,
[UserName] [nvarchar](50) NULL
) ON [PRIMARY]
--INSERT DATA
-----------------
INSERT INTO Table_Dept (DeptId, DeptCode, DeptName)
VALUES (1, 'HO', N'Head Office'),
(2, 'BO', N'Branch Office'),
(3, 'RO', N'Reginal Office'),
(4, 'SO', N'Sub Division Office')
INSERT INTO Table_Header
(Header_Id, UserName)
VALUES (1, N'Ravi'), (2, N'Gopal'), (3, N'Deepa')
INSERT INTO Table_Detail
(Header_ID, Name, DescValue)
VALUES (1, N'ListNumber', N'1005'),
(1, N'PhaseCode', N'AA'),
(1, N'DeptCode', 'BO,RO'),
(1, N'City', 'Bangalore'),
(2, N'ListNumber', N'1006'),
(2, N'PhaseCode', N'AB'),
(2, N'DeptCode', 'SO,RO'),
(2, N'City', 'Delhi'),
(3, N'ListNumber', N'1007'),
(3, N'PhaseCode', N'AA'),
(3, N'DeptCode', 'HO'),
(3, N'City', 'Mumbai')
--EXPECTED RESULT
HeaderIdListNumberPhaseCodeDeptCodeCityUser
11005AABranch Office, Reginal OfficeBangaloreRavi
21006ABSub Division Office,Reginal OfficeDelhiGopal
31007AAHOMumbaiDeepa

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

~~Check out this article. http://www.sqlservercentral.com/articles/71700/[/url]~~

~~It explains very clearly how to do exactly what you are looking for. Please post back if you have any problems getting it to work and we can help.~~

My apologies. I misread what you are trying to do.

What you have here is known as an Entity Attribute Value (EAV) architecture. It is very controversial and can really fuel some fires. Used appropriately this type of architecture is very helpful. Used inappropriately it is very painful to work with.

Thank you very much for posting ddl, sample data and desired output. That makes working on these things so much easier!!!!

Give me a few minutes and I will see what I can come with.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Shanmuga Raj Ten Centuries Points: 1178 More actions · Answer 2

Below is my result query...

SELECT *,

STUFF((SELECT ',' + DeptName

FROM Table_Dept

WHERE ',' + t.DeptCode + ',' LIKE '%,' + DeptCode + ',%'

FOR XML PATH('')),1,1,'') AS DeptName

FROM

(

SELECT h.Header_ID,

td.ListNumber,

td.PhaseCode,

td.DeptCode,

td.City

FROM Table_header h

INNER JOIN (SELECT Header_ID,

MAX(CASE WHEN Name = 'ListNumber' THEN DescValue END) AS ListNumber,

MAX(CASE WHEN Name = 'PhaseCode' THEN DescValue END) AS PhaseCode,

MAX(CASE WHEN Name = 'DeptCode' THEN DescValue END) AS DeptCode,

MAX(CASE WHEN Name = 'City' THEN DescValue END) AS City

FROM Table_Detail

GROUP BY Header_ID)td

ON td.Header_ID = h.Header_ID

)t

Now, I have an additional field where the xml path has to be defined without from table.

..consider the dept code, where the values are now comming from table and need result if the data is not displayed from table and they are static..

use case when 'Bo' then 'Branch Office'

when 'Ro' then 'Reginal Office'

when 'So' then 'Sub Division Office'

when 'Ho' then 'Head Office'

how to replace my above query

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

In addition to having to fight with an EAV you have delimited data stored. This is not a good design choice and if at all possible you should change that. It violates 1NF.

In order to work with this you are first going to have to parse your delimited data into rows so you can use it in your join criteria. Please refer to the article in my signature about splitting strings. In that article you will find the DelimitedSplit8K function.

The code below works with your sample data.

with ParsedData as

(

select h.Header_Id,

ListNum.DescValue as ListNumber,

Phase.DescValue as PhaseCode,

City.DescValue as City,

h.UserName,

d.DeptName

from Table_Header h

left join Table_Detail ListNum on ListNum.Header_ID = h.Header_Id and ListNum.Name = 'ListNumber'

left join Table_Detail Phase on Phase.Header_ID = h.Header_Id and Phase.Name = 'PhaseCode'

left join Table_Detail City on City.Header_ID = h.Header_Id and City.Name = 'City'

left join Table_Detail DeptCode on DeptCode.Header_ID = h.Header_Id and DeptCode.Name = 'DeptCode'

cross apply dbo.DelimitedSplit8K(DeptCode.DescValue, ',') split

join Table_Dept d on d.DeptCode = split.Item

)

select Header_ID,

ListNumber,

PhaseCode,

STUFF((select ', ' + DeptName

from ParsedData d2

where d2.Header_Id = d1.Header_Id

order by d2.DeptName

for xml path('')), 1, 1, '') as BranchOffice,

City,

UserName

from ParsedData d1

group by Header_ID,