July 20, 2012 at 1:21 am
CREATE TABLE [dbo].[TableTemp](
[BASEL_CATEGORY] [varchar](50) NULL,
[EC_DRAWN] [numeric](18, 6) NULL,
[EC_UNDRAWN] [numeric](18, 6) NULL,
[EC_DRAWN_R] [numeric](18, 6) NULL,
[EC_UNDRAWN_R] [numeric](18, 6) NULL,
[EC_DRAWN_S] [numeric](18, 6) NULL,
[METHOD] [varchar](10) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[TableTemp]
([BASEL_CATEGORY]
,[EC_DRAWN]
,[EC_UNDRAWN]
,[EC_DRAWN_R]
,[EC_UNDRAWN_R]
,[EC_DRAWN_S]
,[METHOD])
VALUES
('x'
,0.006
,0.09
,0.098
,1.34
,1.67
,'STD')
INSERT INTO [dbo].[TableTemp]
([BASEL_CATEGORY]
,[EC_DRAWN]
,[EC_UNDRAWN]
,[EC_DRAWN_R]
,[EC_UNDRAWN_R]
,[EC_DRAWN_S]
,[METHOD])
VALUES
('x'
,0.045
,0.037
,0.048
,1.38
,1.60
,'airb')
INSERT INTO [dbo].[TableTemp]
([BASEL_CATEGORY]
,[EC_DRAWN]
,[EC_UNDRAWN]
,[EC_DRAWN_R]
,[EC_UNDRAWN_R]
,[EC_DRAWN_S]
,[METHOD])
VALUES
('y'
,2.006
,7.09
,9.098
,0.34
,7.67
,'STD')
INSERT INTO [dbo].[TableTemp]
([BASEL_CATEGORY]
,[EC_DRAWN]
,[EC_UNDRAWN]
,[EC_DRAWN_R]
,[EC_UNDRAWN_R]
,[EC_DRAWN_S]
,[METHOD])
VALUES
('y'
,5.006
,7.09
,9.098
,2.34
,6.67
,'airb')
select * from [dbo].[TableTemp]
--I need output as following
Output Table
Economic_capitaL X Y Z.......COLUMN HEADERS
EC (drawn) – aIRB
EC (drawn)– Std
EC (undrawn) – AIRB
EC (undrawn) – Std
EC (drawn S) – AIRB
EC (drawn S) – Std
EC (drawn R) – AIRB
EC (drawn R) – Std
EC (undrawn R) – AIRB
EC (undrawn R) – Std
** these are fixed rows
** category have many values all categories should come as column headers
**Note there are only two methods-AIRB ,STD
Please help me in this regard thanks
July 20, 2012 at 1:23 am
Duplicate post. No replies to this thread please. Direct replies to:http://www.sqlservercentral.com/Forums/Topic1332736-3077-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2012 at 1:24 am
Where is the question?
July 20, 2012 at 1:32 am
Hi I have edited the question please reply me its very urgent
July 20, 2012 at 1:35 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Why is this so urgent?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2012 at 1:53 am
CREATE TABLE [dbo].[TableTemp](
[BASEL_CATEGORY] [varchar](50) NULL,
[EC_DRAWN] [numeric](18, 6) NULL,
[EC_UNDRAWN] [numeric](18, 6) NULL,
[EC_DRAWN_R] [numeric](18, 6) NULL,
[EC_UNDRAWN_R] [numeric](18, 6) NULL,
[EC_DRAWN_S] [numeric](18, 6) NULL,
[METHOD] [varchar](10) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[TableTemp]
([BASEL_CATEGORY]
,[EC_DRAWN]
,[EC_UNDRAWN]
,[EC_DRAWN_R]
,[EC_UNDRAWN_R]
,[EC_DRAWN_S]
,[METHOD])
VALUES
('x'
,0.006
,0.09
,0.098
,1.34
,1.67
,'STD')
INSERT INTO [dbo].[TableTemp]
([BASEL_CATEGORY]
,[EC_DRAWN]
,[EC_UNDRAWN]
,[EC_DRAWN_R]
,[EC_UNDRAWN_R]
,[EC_DRAWN_S]
,[METHOD])
VALUES
('x'
,0.045
,0.037
,0.048
,1.38
,1.60
,'airb')
INSERT INTO [dbo].[TableTemp]
([BASEL_CATEGORY]
,[EC_DRAWN]
,[EC_UNDRAWN]
,[EC_DRAWN_R]
,[EC_UNDRAWN_R]
,[EC_DRAWN_S]
,[METHOD])
VALUES
('y'
,2.006
,7.09
,9.098
,0.34
,7.67
,'STD')
INSERT INTO [dbo].[TableTemp]
([BASEL_CATEGORY]
,[EC_DRAWN]
,[EC_UNDRAWN]
,[EC_DRAWN_R]
,[EC_UNDRAWN_R]
,[EC_DRAWN_S]
,[METHOD])
VALUES
('y'
,5.006
,7.09
,9.098
,2.34
,6.67
,'airb')
select * from [dbo].[TableTemp]
--Output Table
Economic_capitaL X Y Z.......COLUMN HEADERS
EC (drawn) – aIRB
EC (drawn)– Std
EC (undrawn) – AIRB
EC (undrawn) – Std
EC (drawn S) – AIRB
EC (drawn S) – Std
EC (drawn R) – AIRB
EC (drawn R) – Std
EC (undrawn R) – AIRB
EC (undrawn R) – Std
** these are fixed rows
** category have many values all categories should come as column headers
**Note there are only two methods
July 20, 2012 at 1:57 am
HI I HAVE POSTED QUESTION AGIAN.. PLS REPLY
July 20, 2012 at 2:35 am
Hi ... can any1 pls help . .
July 20, 2012 at 2:51 am
Patience. We're volunteers here posting in our spare time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2012 at 3:59 am
See if this helps
DECLARE @cols VARCHAR(500)
DECLARE @cmd VARCHAR(5000)
SELECT @cols = STUFF(( SELECT DISTINCT
'],[' + [BASEL_CATEGORY]
FROM [TableTemp]
ORDER BY '],[' + [BASEL_CATEGORY]
FOR
XML PATH('')
), 1, 2, '') + ']'
SET @cmd = '
;WITH C AS (
SELECT * FROM (
SELECT [BASEL_CATEGORY] ,
CASE WHEN [AIRB] = 0 THEN [EC_DRAWN] END AS [EC_DRAWN - STD],
CASE WHEN [AIRB] = 1 THEN [EC_DRAWN] END AS [EC_DRAWN - AIRB],
CASE WHEN [AIRB] = 0 THEN [EC_UNDRAWN] END AS [EC_UNDRAWN - STD],
CASE WHEN [AIRB] = 1 THEN [EC_UNDRAWN] END AS [EC_UNDRAWN - AIRB],
CASE WHEN [AIRB] = 0 THEN [EC_DRAWN_R] END AS [EC_DRAWN_R - STD],
CASE WHEN [AIRB] = 1 THEN [EC_DRAWN_R] END AS [EC_DRAWN_R - AIRB],
CASE WHEN [AIRB] = 0 THEN [EC_UNDRAWN_R] END AS [EC_UNDRAWN_R - STD],
CASE WHEN [AIRB] = 1 THEN [EC_UNDRAWN_R] END AS [EC_UNDRAWN_R - AIRB],
CASE WHEN [AIRB] = 0 THEN [EC_DRAWN_S] END AS [EC_DRAWN_S - STD],
CASE WHEN [AIRB] = 1 THEN [EC_DRAWN_S] END AS [EC_DRAWN_S - AIRB]
FROM ( SELECT [BASEL_CATEGORY] ,
[EC_DRAWN] ,
[EC_UNDRAWN] ,
[EC_DRAWN_R] ,
[EC_UNDRAWN_R] ,
[EC_DRAWN_S] ,
[METHOD]
FROM dbo.TableTemp
) p PIVOT
( COUNT([METHOD]) FOR [METHOD] IN ( [STD], [AIRB] ) ) AS pvt
) AS p2
UNPIVOT
(
[BLAA] FOR [ECONOMIC_CAPITAL] IN (
[EC_DRAWN - STD],
[EC_DRAWN - AIRB],
[EC_UNDRAWN - STD],
[EC_UNDRAWN - AIRB],
[EC_DRAWN_R - STD],
[EC_DRAWN_R - AIRB],
[EC_UNDRAWN_R - STD],
[EC_UNDRAWN_R - AIRB],
[EC_DRAWN_S - STD],
[EC_DRAWN_S - AIRB]
))
AS p3
)
SELECT * FROM C
PIVOT
(
MAX([BLAA])
FOR [BASEL_CATEGORY] IN
( ' + @cols + ' )
) AS pvt
'
EXEC (@cmd)
July 20, 2012 at 4:40 am
Thanks So much 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply