cross tab sql query

gudidi
gudidi used Ask the Experts™
on
Hi  Experts

is there any simple query to make a cross tab?

my data looks like this:

id     name
1      gidi1
1      gidi2
1      gidi3
2      gidi4
2      gidi5

and i want it to look like that:

id          name1   name2    name3
1           gidi1      gidi2       gidi3
2           gidi4      gidi5

10x a lot
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
hi

the main problem is that i cannot use aggregate function for varchar column.
how do i solve this problem?
you can use it... try to use it, it will work
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Commented:
create below function ..(replace ur table and table name and id ..plz check)

use this function once  you create it..
then use like this

select distinct id,[dbo].[Concat_name] (id) from  [dbo].[tbSample]
group by id ,name


replace your table name in [dbo].[tbSample] ,columns id,name

--------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE  function [dbo].[Concat_name] ( @nid int)
returns  varchar(3232)
Begin
declare @result varchar(3232)
declare @result1 varchar(3232)
declare @vCount int
declare @vIndex int
declare @vDelimeter char(3)
declare @nid1 int

set @vCount= (select count(isnull([dbo].[tbSample].[id],0))
 from [dbo].[tbSample] where isnull([dbo].[tbSample].[id],0)= @nid)
set @vDelimeter=','
set @vIndex=1
set @result= ''
set @result1= ''
while @vIndex <=  @vCount

begin
 
set @result1 =
( select e.[Name]
from(select row_number()over (order by [dbo].[tbSample].[name] desc)as rowid, [name]
 from [dbo].[tbSample] where isnull([dbo].[tbSample].[id],0)= @nid) e
where e.rowid = @vIndex
)
if @vIndex<>1
begin
set @result =  @result+','+@result1
end
else
begin
set @result =  @result1
end
set @vIndex = @vIndex+1

end

return @result

end
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE  function [dbo].[Concat_name] ( @nid int)
returns  varchar(3232)
Begin
declare @result varchar(3232)
declare @result1 varchar(3232)
declare @vCount int
declare @vIndex int
declare @vDelimeter char(3)
declare @nid1 int

set @vCount= (select count(isnull([dbo].[tbSample].[id],0))
 from [dbo].[tbSample] where isnull([dbo].[tbSample].[id],0)= @nid)
set @vDelimeter=','
set @vIndex=1
set @result= ''
set @result1= ''
while @vIndex <=  @vCount

begin
 
set @result1 =
( select e.[Name]
from(select row_number()over (order by [dbo].[tbSample].[name] desc)as rowid, [name]
 from [dbo].[tbSample] where isnull([dbo].[tbSample].[id],0)= @nid) e
where e.rowid = @vIndex
)
if @vIndex<>1
begin
set @result =  @result+','+@result1
end
else
begin
set @result =  @result1
end
set @vIndex = @vIndex+1

end

return @result

end

Open in new window

Author

Commented:
i will check it and let you know

Commented:
changes for above function with your desired table (i avoided the cursors as it kills time) :

line 16 :replace your column id

line 17 : replace your table

line 27 : don't replace "e" -that is table alias name ..you can replace name with your coulmn name

line 28 : replace your column name

line 29 : replace your table name

that's it.. create function and use select query from code snippet
in the code ,replace your cloumn names and tables
select distinct id,[dbo].[Concat_name] (id) from  [dbo].[tbSample]
group by id ,name

Open in new window

Data Engineer
Commented:
try like this.

select id,
       max(case rn when 1 then name end) as name1,
       max(case rn when 2 then name end) as name2,
       max(case rn when 3 then name end) as name3
  from (select *,row_number() over (partition by id order by name) as rn
          from your_table) as t1
 group by id
declare @table as table(id int,name varchar(10))
insert into @table values(1,      'gidi1')
insert into @table values(1 ,     'gidi2')
insert into @table values(1  ,    'gidi3')
insert into @table values(2   ,   'gidi4')
insert into @table values(2    ,  'gidi5')

select id,
       max(case rn when 1 then name end) as name1,
       max(case rn when 2 then name end) as name2,
       max(case rn when 3 then name end) as name3
  from (select *,row_number() over (partition by id order by name) as rn 
          from @table) as t1
 group by id
/*
1	gidi1	gidi2	gidi3
2	gidi4	gidi5	NULL
*/

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial