Denormalizing Data... 2

[hneal98]

I am using MS SQL Server 2000. I have a table that has multiple elements for each ID, so when i list them out, I get multiple records for the same ID. I would like to create a separate field for each element so in the end, that ID only lists once as a single record.

here is an example:

A 1
A 2
A 3
B 1
B 2
C 1
C 3
D 1

This is how I would like it to be:

A 1 2 3
B 1 2
C 1 3
D 1

One final thought, i would like these in separate fields, not in a list in one field.

Any help would be appreciated.

Thanks.

 

[sunila7]

Is there a maximum no of rows a particular group can have? or can it have any no of rows per group?

Sunil

 

[hneal98]

Yes. The max is 15.

Thanks.

 

[hneal98]

Also, even though this particular item has a max of 15 rows, if you know how to make it more dynamic where the number of rows could be any amount, I have had many occasions where i wished i could do that, so if you have an example of that; that would be great.

Thanks.

 

[sunila7]

Is the data u have given real data and does the second column you have shown have numeric data as in your example?


Sunil

 

[hneal98]

The actual data is different from what I showed. That is just an example, but the data is set up similar. Also, the data is Char. I work in a Health Insurance company and the data is actually DRGs. These are codes the Hospital uses when billing.

Thanks.

 

[kkielak]

Hello,

this is simple and 'dirty' solution for your problem using stored procedure instead of insert into denormalized table ... maybe it will help

best regards
Chris




create table t (
id varchar(10),
a int
)
go

insert into t values('A',1)
insert into t values('A',2)
insert into t values('A',3)
insert into t values('B',1)
insert into t values('B',2)
insert into t values('C',1)
insert into t values('C',3)
insert into t values('D',1)

select
max(t2.num)
from
(select distinct id, count(a) num from t group by id) t2

-- lets create destination table
create table tdist (
id varchar(10),
int_c int default(0), -- this is internal counter of last inserted value
a1 int,
a2 int,
a3 int,
a4 int,
a5 int,
a6 int,
a7 int,
a8 int,
a9 int,
a10 int,
a11 int,
a12 int,
a13 int,
a14 int,
a15 int
)

-- now let's create proc for inserting one row of data

create procedure sp_insert_denorm
@myid varchar(10),
@mya int
as
begin

declare @p1 int
declare @sql varchar(255)

if exists (select id from tdist where id=@myid)
begin
select @p1=int_c from tdist where id=@myid
set @sql = 'update tdist set a' + CONVERT(varchar(5), @p1) + '=' + CONVERT(varchar(5), @mya) + ' where id=''' + CONVERT(varchar(5), @myid) + ''''
exec (@sql)
update tdist set int_c = @p1 + 1
end
else
insert into tdist (id,int_c, a1) values (@myid,2,@mya)
end
go


exec sp_insert_denorm 'A',1
exec sp_insert_denorm 'A',2
exec sp_insert_denorm 'A',3
exec sp_insert_denorm 'B',1
exec sp_insert_denorm 'B',2
exec sp_insert_denorm 'C',1
exec sp_insert_denorm 'C',3
exec sp_insert_denorm 'D',1

go



2> select id,a1,a2,a3,a4 from tdist
3> go
id a1 a2 a3 a4
---------- ----------- ----------- ----------- -----------
A 1 2 3 NULL
B 1 2 NULL NULL
C 1 3 NULL NULL
D 1 NULL NULL NULL

(4 rows affected)

 

[hneal98]

Thanks Chris. I will give it a try and let you know.

 

[kkielak]

... in fact only definition of the tdist counts and stored procedure ... the rest is just copy from my notepad - sorry .

For each row in tdist you have int_c which says how many columns are populated

Chris

 

[sunila7]

Try this: this should work for any no of rows for a group. I wanted to avoid using cursor so came up with a numbering sequence for a record. Hope it helps

<Sample Data I used >
Create table #t(f1 varchar, f2 int )
insert into #t values('A', 1)
insert into #t values('A', 2)
insert into #t values('A', 3)

insert into #t values('B', 1)
insert into #t values('B', 2)
insert into #t values('B', 3)

insert into #t values('C', 2)
insert into #t values('C', 3)
insert into #t values('C', 4)

<code>
-- Create a temp table to hold the Numeric coulmn
SELECT f1,f2, (SELECT count(*) from #t t where t.f1= t1.f1 and t.f2<=t1.f2) f3 into #t1
from #t t1 -- Insert numeric fld into the temp table


--Construct SQL string and execute
Declare @Maxcnt int
Declare @Cnt int
select @maxcnt = max(f3) from #t1

SET @cnt = 2

Declare @SQL1 varchar(4000)
Declare @SQL2 varchar(4000)
Declare @SQL3 varchar(4000)

SET @SQL1 = 'SELECT f1,'
SET @SQL2 = '(SELECT f1,'

While @Maxcnt <> 0
Begin
SET @SQL1 = @SQL1 + ' Max(f'+ cast(@cnt as varchar) + ') ' + 'f' + cast(@cnt as varchar) + ','
SET @SQL2 = @SQL2 + 'CASE when f3 = '+ cast(@cnt-1 as varchar) + ' then f2 end f' + cast(@cnt as varchar) + ','
SET @cnt = @cnt+1
SET @Maxcnt = @Maxcnt -1
End
SET @SQL1 = LEFT(@SQL1,len(@SQL1) -1)
SET @SQL2 = LEFT(@SQL2,len(@SQL2) -1)
SET @SQL3 = ' FROM #t1) TBL Group by f1'
exec (@SQL1 + ' FROM ' + @SQL2 + @SQL3)
</code>

Sunil

 

[kkielak]

Sunila,

your code is much better (and look much more professional)... i have to work harder next time

Chris

 

[sunila7]

Thanks Chris..

Sunil

 

[hneal98]

Wow. This is awsome. Thanks Sunil. This works great. I will have to make some modifications, but they will be minor.

Thanks again. I am awording a start for this.

I thank you too kkielak. Your idea would have worked to, but on a more limited basis.