select 10 column in 1 row and remainng in next row

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

SQL Server 2008 Forums

Transact-SQL (2008)

select 10 column in 1 row and remainng in next row

Author

Topic

NITINMITTAL01
Starting Member

2 Posts

Posted - 2012-05-15 : 16:32:20

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|MomentumETF40Blend_MX0173|idx000049|1|idx000091|1|idx000014|1|idx000022|11|
MX0173|MomentumETF40Blend_MX0173|idx000028|2|idx000091|2|idx000103|2|idx000046|2|
MX0173|MomentumETF40Blend_MX0173|idx000044|2|idx000050|2|idx000051|2||3|idx000063|31|idx000091|5|idx000091|5|idx000043|6|idx000023|7||7|idx000033|8

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-15 : 21:56:55

[code]
; with data as
(
select [composite_id], [composite_name], [allocation], [weight],
grp = (row_number() over (partition by [composite_id], [composite_name] order by [allocation]) - 1) / 10,
row_no = row_number() over (partition by [composite_id], [composite_name] order by [allocation])
from custom
)
select d.[composite_id] + '|'
+ d.[composite_name] + '|'
+ stuff((
select ',' + [allocation] + '|' + [weight]
from data x
where x.grp = d.grp
order by x.row_no
for xml path('')
), 1, 1, '')
from data d
group by d.[composite_id], d.[composite_name], d.[grp]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources