July 8, 2011 at 4:41 am
Please help.
I need to create a column to place the only changing cell in a group of rows into columns items and so have one row for that group with changing item in columns.
Sample data is as follows:
create table CCtest (
localID varchar(50),
STDate varchar(10),
enddate varchar(10),
CCAC varchar(5))
insert into CCtest Values ('57180', '20110601', '20110601', '3')
insert into CCtest Values ('57181', '20110601', '20110601', '3')
insert into CCtest Values ('57181', '20110601', '20110601', '21')
insert into CCtest Values ('57181', '20110601', '20110601', '21')
insert into CCtest Values ('57182', '20110601', '20110601', '28')
insert into CCtest Values ('65652', '20110415', '20110420', '21')
insert into CCtest Values ('65652', '20110415', '20110420', '25')
insert into CCtest Values ('65652', '20110415', '20110420', '28')
insert into CCtest Values ('65652', '20110415', '20110420', '29')
insert into CCtest Values ('65652', '20110415', '20110420', '29')
insert into CCtest Values ('65652', '20110415', '20110420', '74')
insert into CCtest Values ('75944', '20110614', '20110614', '8')
insert into CCtest Values ('75944', '20110614', '20110614', '9')
insert into CCtest Values ('75944', '20110614', '20110614', '21')
insert into CCtest Values ('75944', '20110614', '20110614', '22')
insert into CCtest Values ('75943', '20110613', '20110613', '8')
insert into CCtest Values ('75943', '20110613', '20110613', '9')
insert into CCtest Values ('75943', '20110613', '20110613', '21')
insert into CCtest Values ('75943', '20110613', '20110613', '22')
insert into CCtest Values ('97446', '20110510', '20110510', '9')
insert into CCtest Values ('97446', '20110510', '20110510', '22')
insert into CCtest Values ('97446', '20110510', '20110510', '23')
insert into CCtest Values ('97446', '20110510', '20110510', '28')
insert into CCtest Values ('97446', '20110510', '20110510', '29')
insert into CCtest Values ('97445', '20110509', '20110509', '3')
insert into CCtest Values ('97445', '20110509', '20110509', '9')
insert into CCtest Values ('97445', '20110509', '20110509', '9')
insert into CCtest Values ('97445', '20110509', '20110509', '22')
insert into CCtest Values ('97445', '20110509', '20110509', '23')
CCAC is the one I want as column heads. The constraint I have is that there can be as many as 70 different values, but each row can only have a maximum of 20 different values. So I can only have 20 column heads going across.
The output will look like this:
localIDSTDate enddate CCAC1 CCAC2 CCAC3 CCAC4 CCAC5 CCAC6
5718020110601201106013
57181201106012011060132121
57182201106012011060128
656522011041520110420212528292974
759442011061420110614892122
759432011061320110613892122
974462011051020110510922232829
974452011050920110509392223
With the CCAC going up to CCAC20
Can anyof you very clever people help please
July 8, 2011 at 5:06 am
Hi
A couple of questions:
If the same CCAC value is entered more than once should it only be displayed once in the output?
e.g.
For LocalID 97445 I get an output of 3 9 9 22 23, but you have got 3 9 22 23
Do you have any control over the datatypes you are using? If you can change CCAC to be an integer it would make it easier to use a pivot
Thanks
July 8, 2011 at 5:26 am
Really appreciate you helping:
The same CCAC value will not be entered more than once for each group of rows. I also can change the data type to integer.
However, remember I need all the CCAC values in sequence from CCAC1, and there can only be a maximum of 20 CCACs for any row, and so the table the output is going into only has 20 CCAC columns.
I am not very good with pivot, but wouldn't you have to have as many CCAC columns as there are in the entire dataset (about 70 in the actual data)?
Thanks for helping again. Looking forward to your reply
July 8, 2011 at 6:00 am
Think this does what you are after - let me know if not D..
declare @ccTest table (
localID varchar(50),
STDate varchar(10),
enddate varchar(10),
CCAC int)
insert into @ccTest Values ('57180', '20110601', '20110601', 3)
insert into @ccTest Values ('57181', '20110601', '20110601', 3)
insert into @ccTest Values ('57181', '20110601', '20110601', 21)
insert into @ccTest Values ('57181', '20110601', '20110601', 21)
insert into @ccTest Values ('57182', '20110601', '20110601', 28)
insert into @ccTest Values ('65652', '20110415', '20110420', 21)
insert into @ccTest Values ('65652', '20110415', '20110420', 25)
insert into @ccTest Values ('65652', '20110415', '20110420', 28)
insert into @ccTest Values ('65652', '20110415', '20110420', 29)
insert into @ccTest Values ('65652', '20110415', '20110420', 29)
insert into @ccTest Values ('65652', '20110415', '20110420', 74)
insert into @ccTest Values ('75944', '20110614', '20110614', 8)
insert into @ccTest Values ('75944', '20110614', '20110614', 9)
insert into @ccTest Values ('75944', '20110614', '20110614', 21)
insert into @ccTest Values ('75944', '20110614', '20110614', 22)
insert into @ccTest Values ('75943', '20110613', '20110613', 8)
insert into @ccTest Values ('75943', '20110613', '20110613', 9)
insert into @ccTest Values ('75943', '20110613', '20110613', 21)
insert into @ccTest Values ('75943', '20110613', '20110613', 22)
insert into @ccTest Values ('97446', '20110510', '20110510', 9)
insert into @ccTest Values ('97446', '20110510', '20110510', 22)
insert into @ccTest Values ('97446', '20110510', '20110510', 23)
insert into @ccTest Values ('97446', '20110510', '20110510', 28)
insert into @ccTest Values ('97446', '20110510', '20110510', 29)
insert into @ccTest Values ('97445', '20110509', '20110509', 3)
insert into @ccTest Values ('97445', '20110509', '20110509', 9)
insert into @ccTest Values ('97445', '20110509', '20110509', 9)
insert into @ccTest Values ('97445', '20110509', '20110509', 22)
insert into @ccTest Values ('97445', '20110509', '20110509', 23)
;with cte as (
select *
,row_number() over (partition by localid, stdate, enddate order by ccac) seq
from @cctest
)
select LocalID, STDate, EndDate
,[1] CACC1
,[2] CACC2
,[3] CACC3
,[4] CACC4
,[5] CACC5
,[6] CACC6
,[7] CACC7
,[8] CACC8
,[9] CACC9
,[10] CACC10
,[11] CACC11
,[12] CACC12
,[13] CACC13
,[14] CACC14
,[15] CACC15
,[16] CACC16
,[17] CACC17
,[18] CACC18
,[19] CACC19
,[20] CACC20
from (select localid, stdate, enddate, seq, ccac
from cte) as p
pivot
(max(ccac)
for
seq in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20])
)
as pvt
July 8, 2011 at 6:05 am
I think this does what you want it to
I changed the datatype of CCAC to integer and then ran the data into a temporary table to make use of the row_number() function to give each record an incrementing ID number
select *, row_number() over (partition by LocalID order by CCAC) RowNum
into #Tmp_Results
from cctest
And then pivoted the results. For brevity I have only gone up to 10 CCAC fields in the output so you will need to add CCAC 11-20.
SELECT LocalID, STDate, EndDate, isnull(P.[1],'') as CCAC1, isnull(P.[2],'') as CCAC2, isnull(P.[3],'') as CCAC3,
isnull(P.[4],'') as CCAC4, isnull(P.[5],'') as CCAC5, isnull(P.[6],'') as CCAC6, isnull(P.[7],'') as CCAC7,
isnull(P.[8],'') as CCAC8, isnull(P.[9],'') as CCAC9, isnull(P.[10],'') as CCAC10
FROM #Tmp_Results
PIVOT (
avg(CCAC) FOR
RowNum IN ([1], [2], [3], [4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20])
) P;
I have used isnull() to present blank CCAC fields in a nicer way to the end user - if you need blank CCAC fields to be empty rather than 0 you could do something like this to convert the CCAC values back into strings and then show an empty string for any NULL fields
isnull(cast(P.[5] as varchar(5)),'')
July 8, 2011 at 6:07 am
Curse my slow typing
:blush:
July 8, 2011 at 6:08 am
But you do have a fuller explanation... 😛 and therefore a more complete reply - curse my haste !!
July 8, 2011 at 6:25 am
This is remarkable. Thank you.
It worked and I will have to study whats happening to apply it.
The table I am working on has thousands of rows and many more columns. Is there anyway I can run it from that table as it will be very difficult to recreate it as a variable like you have done
I await your answer
But thank you sooooooooooooo much for your suggestion so far!
July 8, 2011 at 6:35 am
from my code snippet - change the from clause to point to your table 🙂
;with cte as (
select *
,row_number() over (partition by localid, stdate, enddate order by ccac) seq
from @cctest
)
July 8, 2011 at 6:42 am
Fantastic. Really grateful.
Will test it out shortly and let you know
July 8, 2011 at 6:47 am
If you need to run the pivot directly from your source table without using either a cte or #temp table, you need to somehow add the row number into the source table
If you can get the application that writes to the table to do this for you somehow, then you can just run directly off the table
If not, you will need to work out the row number yourself
In order to get this to work I had to remove all the records with the same CCAC for a single LocalID from the cctest table (which I don't think would exist for the real data?) and then added a new RowNum column to the table.
alter table cctest add RowNum smallint
I then ran an update statement against this to populate the RowNum column
update t
set t.RowNum = z.RowNum
--select *
from cctest t
inner join
(select localid, ccac, row_number() over (partition by localid order by ccac) RowNum
from cctest) z
on t.localid = z.localid
and t.ccac = z.ccac
You can now point the pivot to the cctest table rather than the #tmp_results table and it will produce the results you require
The downside of doing this, is that you would need to perform the update against thousands of rows, and then re-run it the next time you wanted to look at the data in a pivot format.
If you know there is a certain window of opportunity that an update can happen to a LocalID, and that after that you no longer have to worry about the CCAC values changing, then you could build that into the update to limit the number of rows it tries to change
July 8, 2011 at 6:57 am
I am ever so grateful. This willl certainly leverage my SQL skill
Thanks guys
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply