pivot with header and details - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

pivot with header and details

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2014-02-03 : 09:53:43

I need to display data from both header and detail, details has to come from other table also

--CREATE TABLE
-----------------

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_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

HeaderId ListNumber PhaseCode DeptCode City User
1 1005 AA Branch Office, Reginal Office Bangalore Ravi
2 1006 AB Sub Division Office,Reginal Office Delhi Gopal
3 1007 AA HO Mumbai Deepa

.
..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

Belwo is my sql

SELECT *
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

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-02-03 : 23:16:46

SELECT Header_Id
, ListNumber
, PhaseCode
, CASE WHEN DeptCode = 'Bo,Ro' THEN 'Branch Office,Reginal Office'
WHEN DeptCode = 'So,Ro' THEN 'Sub Division Office,Reginal Office'
WHEN DeptCode = 'Ho' THEN 'Head Office' ELSE '' END AS 'DeptCode'
, City
, UserName
FROM
(
SELECT h.Header_ID,
td.ListNumber,
td.PhaseCode,
td.DeptCode,
td.City,
h.UserName
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

Veera

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2014-02-04 : 02:56:28

Thanks Veera

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-02-04 : 03:04:33

Welcome

Veera

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-04 : 06:18:22

quote:
Originally posted by shanmugaraj

Thanks Veera

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

Didnt I gave you solution in other forum. Didnt it work for you?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources