December 17, 2008 at 8:55 am
Hi Friends,
I need a help in constructing a query for pivot a table values
I am having a table like below
[CREATE TABLE [dbo].[Output_Info](
[Common_Id] [int] NOT NULL,
[Signal] [real] NOT NULL,
[Detect] [nchar](10) NOT NULL,
[PValue] [real] NOT NULL,
[EAID] [int] NOT NULL,
)]
INSERT INTO [MDADB].[dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(1,12.3,'P',42.5,22)
INSERT INTO [MDADB].[dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(1,12.3,'P',32.5,23)
INSERT INTO [MDADB].[dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(2,32.3,'P',43.5,22)
INSERT INTO [MDADB].[dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(2,8.3,'P',2.5,23)
For each common_Id, I would like to get the rows as
common_Id Signal_EAID,Detect_EAID,PValue_EAID
sO THE result column headers should look like this
commonId Signal_22 Detect_22 PValue_22 Signal_23 Detect_23 PValue_23
1 12.3 p 42.5 12.3 P 32.5
2 32.3 P 43.5 8.3 P 2.5
Is it possible to do this?
I am confused about this and got stuck here.
Please help me.
Thanks,
-Maria
December 17, 2008 at 2:30 pm
I'm not sure how to approach this with PIVOT because you aren't grouping on EAID, you are actually using it to divide into two sets of columns. You can get the result you wanted by using a summary query that gives you the max values of several case expressions. This solution assumes that the only values in EAID are 22 or 23, and that you will not have multiple rows with a given Common_ID/EAID combination.
--------------------------------------------------------------------------------------------
-- most of this is just setup of your data, skip to the bottom for the summary query
--------------------------------------------------------------------------------------------
declare @output_info TABLE (
[Common_Id] [int] NOT NULL,
[Signal] [real] NOT NULL,
[Detect] [nchar](10) NOT NULL,
[PValue] [real] NOT NULL,
[EAID] [int] NOT NULL
)
INSERT INTO @output_info ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(1,12.3,'P',42.5,22)
INSERT INTO @output_info([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(1,12.3,'P',32.5,23)
INSERT INTO @output_info ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(2,32.3,'P',43.5,22)
INSERT INTO @output_info ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(2,8.3,'P',2.5,23)
select * from @output_info
---------------------- Everything above was just to set up. This is how we roll. ----------
select common_id
,max(case when EAID = 22 then Signal else null end) as Signal22
,max(case when EAID = 22 then Detect else null end) as Detect22
,max(case when EAID = 22 then Pvalue else null end) as Pvalue22
,max(case when EAID = 23 then Signal else null end) as Signal23
,max(case when EAID = 23 then Detect else null end) as Detect23
,max(case when EAID = 23 then Pvalue else null end) as Pvalue23
from @output_info -- change this to your table name
group by common_id
order by common_id
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 17, 2008 at 2:38 pm
Hi,
Thanks for your reply.
Actually, the EAID is dynamic. It will vary and so I will have to have dynamic column.
How will I write a query in such a situation.
Thanks in advance.
-Maria
December 17, 2008 at 2:44 pm
Neither my approach nor PIVOT is able to create the column names for you. You have to identify all of them in advance, which means you would have to identify all possible values for EAID in advance. If you cannot do that, you will have to do a select distinct EAID and use those values to create and execute dynamic SQL.
Something like this. The @dynsql string is built from opening and closing constants, and in between it replaces the 'XX' strings with the distinct EIAD values from the subquery dt (derived table) to create both the case expressions and your column names.
declare @dynSQL nvarchar(4000)
set @dynsql = 'select common_id'
select @dynsql = @dynsql+replace(',max(case when EAID = XX then Signal else null end) as SignalXX
,max(case when EAID = XX then Detect else null end) as DetectXX
,max(case when EAID = XX then Pvalue else null end) as PvalueXX','XX',EAID)
from (select distinct EAID from output_info) as dt
select @dynSQL = @dynSQL+' from output_info group by common_id order by common_id'
print @dynSQL -- just so you can see what the query looks like
exec sp_executesql @dynsql
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 18, 2008 at 1:18 am
Again using RAC:
Exec Rac
@transform='Max(Signal) as Signal & Max(Detect) as Detect & Max(PValue) as PValue',
@rows='Common_ID',
@pvtcol='EAID',
@from='output_info',
@row_totals='n',@grand_totals='n',@rowbreak='n',
@rotate='nest',
@racheck='y',@shell='n'
Common_ID Signal_22 Detect_22 PValue_22 Signal_23 Detect_23 PValue_23
--------- --------- --------- --------- --------- --------- ---------
1 12.3 P 42.5 12.3 P 32.5
2 32.3 P 43.5 8.3 P 2.5
Post back if you have questions. Pretty easy huh:)
Visit RAC @
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply