October 1, 2007 at 1:49 pm
Hi,
here is my problem
i've a table in my database like this
Col1 | Col 2
____|______
aaa | 111
aaa | 222
aaa | 333
bbb | 222
bbb | 333
i would like to make a dataset like that
to insert data into a flat text file or another table
Col1 | Col2 | Col3 | Col4
______|______|______|______
aaa | 111 | 222 | 333
bbb | 222 | 333 | NULL
the table is quite big, so i can't make it line by line
i would like to have a maximum of 12 column ( if there is more data than 12, these data should be ignored )
thanks for answering
October 3, 2007 at 7:21 am
edony44,
Without table schema/structure, and meaningful data, it is difficult to predict how the data will look.
Please keep in mind to always post schema and meaningful data so that we can be at our best when helping you.
With the information so far, I would assume, that the column names are probably not "Col1 and Col2." I am also assuming that since your posting is in SQL Server 2005, that this is the version you are using.
Quick Sample:
Create Table and Input Sample Data:
if (exists (select 1 from dbo.sysobjects where name='YourTable' and type='u'))
begin
drop table [dbo].[YourTable]
end
create table [dbo].[YourTable]
(
col1 varchar(100)
,col2 varchar(100)
)
declare @i int
set @i = 1
while @i<=10
begin
insert into dbo.YourTable
(
Col1
, Col2
)
select
convert(varchar(10),@i)
,convert(varchar(10),@i*111)
set @i = @i + 1
end
set @i = 1
while @i<=10
begin
insert into dbo.YourTable
(
Col1
, Col2
)
select
convert(varchar(10),@i)
,convert(varchar(10),@i*222)
set @i = @i + 1
end
set @i = 1
while @i<=8
begin
insert into dbo.YourTable
(
Col1
, Col2
)
select
convert(varchar(10),@i)
,convert(varchar(10),@i*333)
set @i = @i + 1
end
set @i = 1
while @i<=5
begin
insert into dbo.YourTable
(
Col1
, Col2
)
select
convert(varchar(10),@i)
,convert(varchar(10),@i*444)
set @i = @i + 1
end
set @i = 1
while @i<=6
begin
insert into dbo.YourTable
(
Col1
, Col2
)
select
convert(varchar(10),@i)
,convert(varchar(10),@i*555)
set @i = @i + 1
end
set @i = 1
while @i<=4
begin
insert into dbo.YourTable
(
Col1
, Col2
)
select
convert(varchar(10),@i)
,convert(varchar(10),@i*777)
set @i = @i + 1
end
set @i = 1
while @i<=4
begin
insert into dbo.YourTable
(
Col1
, Col2
)
select
convert(varchar(10),@i)
,convert(varchar(10),@i*888)
set @i = @i + 1
end
Quick/Dirty SQL Server 2005 Query using PIVOT Table:
select
Col1
,Col2
,Col3
,Col4
,Col5
,Col6
,Col7
,Col8
,Col9
,Col10
,Col11
,Col12
from
(
select
Helper2.Col1
, (
case
when (Helper2.RowsLTE = 1)
then 'Col2'
when (Helper2.RowsLTE = 2)
then 'Col3'
when (Helper2.RowsLTE = 3)
then 'Col4'
when (Helper2.RowsLTE = 4)
then 'Col5'
when (Helper2.RowsLTE = 5)
then 'Col6'
when (Helper2.RowsLTE = 6)
then 'Col7'
when (Helper2.RowsLTE = 7)
then 'Col8'
when (Helper2.RowsLTE = 8)
then 'Col9'
when (Helper2.RowsLTE = 9)
then 'Col10'
when (Helper2.RowsLTE = 10)
then 'Col11'
when (Helper2.RowsLTE = 11)
then 'Col12'
end
) as [Col2Name]
, Helper2.Col2 as [Col2Value]
from
(
select
a.Col1
, a.Col2
, count(a.Col2) as RowsLTE -- Row values Less than or equal to me
from dbo.YourTable a, dbo.YourTable b
where
a.Col1 = b.Col1
and
a.Col2 > b.Col2
group by a.Col1, a.Col2
) as Helper2
) p PIVOT
(
MAX(Col2Value)
FOR [Col2Name]
in ([Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [Col8], [Col9], [Col10], [Col11], [Col12])
) as pvt
order by Col1
Regards,
Wameng Vang
MCTS
October 3, 2007 at 12:02 pm
thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply