May 12, 2012 at 9:06 am
I have data like this
COMID ALLOCATION
1 10
1 230
2 1222
3 2
4 33
1 33
But i want it like this
comid all1 all2 all3
1 10 230 33
2 1222 0 0
3 2 0 0
thanks
May 12, 2012 at 9:18 am
Will there always be only 3 rows for each COMMID?
If Yes, then what order do those 3 rows be pivoted? I dont see any unique sort order for the table, so all1 or all2 or all3 may contain any of the rows.
As a starter, here is a small script to keep the ball rolling
;WITH Tab (COMID, ALLOCATION) AS
(
SELECT 1, 10
UNION ALL SELECT 1, 230
UNION ALL SELECT 2, 1222
UNION ALL SELECT 3, 2
UNION ALL SELECT 4, 33
UNION ALL SELECT 1, 33
)
, Numbered AS
(
SELECT T.COMID
, T.ALLOCATION
, RN = ROW_NUMBER() OVER (PARTITION BY T.COMID ORDER BY T.ALLOCATION )
FROM Tab T
)
SELECT N.COMID
,All1 = MAX( CASE WHEN N.RN =1 THEN N.ALLOCATION ELSE 0 END )
,All2 = MAX( CASE WHEN N.RN =2 THEN N.ALLOCATION ELSE 0 END )
,All3 = MAX( CASE WHEN N.RN =3 THEN N.ALLOCATION ELSE 0 END )
FROM Numbered N
GROUP BY N.COMID
May 12, 2012 at 7:31 pm
Thanks for the reply !!
But there can be thousand of Comid , is there any other way to to do this...
May 12, 2012 at 10:21 pm
nitin_456 (5/12/2012)
Thanks for the reply !!But there can be thousand of Comid , is there any other way to to do this...
Understood on there being thousands of Comids but that wasn't the question. How many allocations can you have per Comid? If the answer is "unknown", then have a look at the following article for how to easily do dynamic cross tabs.
http://qa.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
May 13, 2012 at 11:56 pm
Jeff Moden (5/12/2012)
nitin_456 (5/12/2012)
Thanks for the reply !!But there can be thousand of Comid , is there any other way to to do this...
Understood on there being thousands of Comids but that wasn't the question. How many allocations can you have per Comid? If the answer is "unknown", then have a look at the following article for how to easily do dynamic cross tabs.
+1.
Looks like you need a Dynamic Cross Tab.
May 14, 2012 at 4:18 pm
There can be maximum of 20 allocation per comm id and how can I do pivot on multiple columns.
May 14, 2012 at 5:25 pm
nitin_456 (5/14/2012)
There can be maximum of 20 allocation per comm id and how can I do pivot on multiple columns.
It's easy. "Must look eye." Take a look at the link I posted in my previous post on this thread.
--Jeff Moden
May 15, 2012 at 7:25 am
Because you know that you have a maximum of 20 values per ComId, you could do this without a dynamic pivot by pre-numbering the data with a row_number:
-- create a temporary table and populate test data
-- you'll win friends and influence people if you do this in your original post!
create table #tmp (
ComId int
, Allocation int
)
insert #tmp values (1,10)
insert #tmp values (1,230)
insert #tmp values (2,1222)
insert #tmp values (3,2)
insert #tmp values (4,33)
insert #tmp values (1,33)
-- convert the data
; with cte as (
select ComId
, Allocation
, row_number() over (partition by ComId order by Allocation) as Picker
from #tmp
)
select ComId
, isnull([1],0) as All1
, isnull([2],0) as All2
, isnull([3],0) as All3
, isnull([4],0) as All4
, isnull([5],0) as All5
, isnull([6],0) as All6
, isnull([7],0) as All7
, isnull([8],0) as All8
, isnull([9],0) as All9
, isnull([10],0) as All10
, isnull([11],0) as All11
, isnull([12],0) as All12
, isnull([13],0) as All13
, isnull([14],0) as All14
, isnull([15],0) as All15
, isnull([16],0) as All16
, isnull([17],0) as All17
, isnull([18],0) as All18
, isnull([19],0) as All19
, isnull([20],0) as All20
from (
select ComId
, Allocation
, Picker
from cte
) sel
pivot (
max(Allocation) for Picker in (
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20] -- max 20 values as per OP
)
) pvt
* obviously, you should replace the #tmp table name with the name of your table.
Note that the order by clause of the row_number will mean that your values are returned in ascending order left to right. If you don't want this, change the order by clause to use the ComId. This query will run much better if you have an index on the ComId column, plus any columns that you use for the order by clause of the row_number and including the Allocation column.
Essentially the same solution as Jeff's, but with pivot instead of case and a larger number of returned columns. I believe that individual case statements are more efficient than pivot, I just like the readability of the pivot operator.
Regards, Iain
Edit: various derp
May 15, 2012 at 11:25 am
Thanks Everyone for your Time and effort!!!
I have used Dynamic Cross tab to deal with this thing, Now i need help in sending pipe delimited data to a text file..
Scripts are here:
1) Create Script
CREATE TABLE [dbo].[custom](
[composite_id] [varchar](2000) NULL,
[composite_name] [varchar](2000) NULL,
[allocation] [varchar](2000) NULL,
[weight] [varchar](2000) NULL
) ON [PRIMARY]
2) Insert statement
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000063','31');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000022','11');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000033','8');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000023','7');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','','7');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000043','6');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000091','5');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000091','5');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','','3');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000091','2');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000103','2');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000046','2');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000028','2');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000044','2');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000050','2');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000051','2');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000049','1');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000091','1');
insert into custom values ('MX0173', 'MomentumETF40Blend_MX0173','idx000014','1');
3)Dynamic Sql to transpose
DECLARE @SQL NVARCHAR(MAX),
@Loop INT,
@MaxRows INT,
@sloop INT
SET @Sql = ''
SELECT TOP 1 @MaxRows= count(*)
FROM custom
GROUP BY composite_id
order by count(*) DESC
SET @Loop = 1
SET @sloop=10
WHILE @Loop <= @MaxRows
BEGIN
SELECT @SQL = @SQL + ', MAx(CASE WHEN Row = ' + CAST(@Loop AS VARCHAR(10)) + ' THEN ' + QUOTENAME(Column_Name) + ' END) AS [' + COLUMN_NAME + CAST(@Loop AS VARCHAR(10)) + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_Name = 'Custom'
AND COLUMN_NAME NOT IN ('Composite_id','composite_name')
SET @Loop = @Loop + 1
END
SET @SQL = 'SELECT Composite_id,rtrim(composite_name)' + @SQL + ' FROM (select *, row_number() over (partition by composite_id ORDER BY weight) as Row FROM Custom) X GROUP BY composite_id,composite_name '
--PRINT @SQL
PRINT @SQL
EXECUTE( @SQL)
What I need output dynamically like this
MX0173|Momentum ETF 40 Blend_MX0173|idx000049|1|idx000091|1|idx000014|1|idx000022|11|idx000028|2|idx000091|2|idx000103|2|idx000046|2|idx000044|2|idx000050|2|idx000051|2||3|idx000063|31|idx000091|5|idx000091|5|idx000043|6|idx000023|7||7|idx000033|8
Please help !!!
Thanks
May 15, 2012 at 6:58 pm
nitin_456 (5/15/2012)
I have used Dynamic Cross tab to deal with this thing, Now i need help in sending pipe delimited data to a text file..
As a matter of ettiquette, I'd like to make two recommendations...
1. Post your Dynamic Cross Tab solution so that others may benefit from your new found skill.
2. When asking a new question having nothing to do with the original question, start a new post so that others might search for it more easily.
Thanks.
--Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply