Creating a Crosstab in SQL

Hello,
I have data in a table as follows:
number type percent
147823 MTOR 100
147823 CLOR 100
147964 CLOR 100
148078 MTOR 50
148126 CLOR 100
148126 MTOR 100
How do I write a sql to get it as follows:
number CLOR MTOR
147823 100 100
147964 100
148078 50
148126 100 100
Thanks
deodevIs this SQL 2005?|||Here are 2 ways...
--data
declare @.t table (number int, type char(4), [percent] int)
insert @.t
select 147823, 'MTOR', 100
union all select 147823, 'CLOR', 100
union all select 147964, 'CLOR', 100
union all select 148078, 'MTOR', 50
union all select 148126, 'CLOR', 100
union all select 148126, 'MTOR', 100
--calculation (one way)
select
number,
max(case when type = 'CLOR' then [percent] else null end) as CLOR,
max(case when type = 'MTOR' then [percent] else null end) as MTOR
from @.t
group by number
order by number
--calculation (another way)
select isnull(a.number, b.number) as number, a.[percent] as CLOR,
b.[percent] as MTOR
from
(select number, [percent] from @.t where type = 'CLOR') a full outer join
(select number, [percent] from @.t where type = 'MTOR') b on a.number =
b.number
order by number
"deodev" wrote:

> Hello,
> I have data in a table as follows:
> number type percent
> 147823 MTOR 100
> 147823 CLOR 100
> 147964 CLOR 100
> 148078 MTOR 50
> 148126 CLOR 100
> 148126 MTOR 100
> How do I write a sql to get it as follows:
> number CLOR MTOR
> 147823 100 100
> 147964 100
> 148078 50
> 148126 100 100
> Thanks
>
> --
> deodev|||Pain in the kiester :(|||Thanks ryan for the help :P This will work as well. Thanks to your
leet sql ninjaness :P
CREATE TABLE #TABLE ( NUMBER INT, COLUMN_NAME NVARCHAR(4), [PERCENT]
INT)
INSERT INTO #TABLE
VALUES(147823,'MTOR',100)
INSERT INTO #TABLE
VALUES(147823,'CLOR',100)
INSERT INTO #TABLE
VALUES(147964,'CLOR',100)
INSERT INTO #TABLE
VALUES(148078,'MTOR',50)
INSERT INTO #TABLE
VALUES(148126,'CLOR',100)
INSERT INTO #TABLE
VALUES(148126,'MTOR',100 )
DECLARE @.QUERY NVARCHAR(MAX)
SET @.QUERY=''
SET @.QUERY='SELECT NUMBER'
DECLARE @.COLUMNS TABLE ( COLUMN_NAME NVARCHAR(4) )
DECLARE CURSOR_COLUMNS CURSOR READ_ONLY
FOR SELECT DISTINCT COLUMN_NAME FROM #TABLE ORDER BY COLUMN_NAME
DECLARE @.COLUMN_NAME nvarchar(4)
OPEN CURSOR_COLUMNS
FETCH NEXT FROM CURSOR_COLUMNS INTO @.COLUMN_NAME
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
SET @.QUERY = @.QUERY + ',MAX(CASE WHEN COLUMN_NAME = ''' +
@.COLUMN_NAME + ''' THEN [PERCENT] ELSE NULL END) AS ' + @.COLUMN_NAME
END
FETCH NEXT FROM CURSOR_COLUMNS INTO @.COLUMN_NAME
END
SET @.QUERY = @.QUERY + ' FROM #TABLE GROUP BY NUMBER ORDER BY NUMBER'
CLOSE CURSOR_COLUMNS
DEALLOCATE CURSOR_COLUMNS
PRINT @.QUERY
EXEC sp_executesql @.QUERY
DROP TABLE #TABLE|||This seems OK for a small data set -
how would I code if I have about 500 number
--
PS. It is SQL 2005
deodev
"Ryan Randall" wrote:
> Here are 2 ways...
> --data
> declare @.t table (number int, type char(4), [percent] int)
> insert @.t
> select 147823, 'MTOR', 100
> union all select 147823, 'CLOR', 100
> union all select 147964, 'CLOR', 100
> union all select 148078, 'MTOR', 50
> union all select 148126, 'CLOR', 100
> union all select 148126, 'MTOR', 100
> --calculation (one way)
> select
> number,
> max(case when type = 'CLOR' then [percent] else null end) as CLOR,
> max(case when type = 'MTOR' then [percent] else null end) as MTOR
> from @.t
> group by number
> order by number
> --calculation (another way)
> select isnull(a.number, b.number) as number, a.[percent] as CLOR,
> b.[percent] as MTOR
> from
> (select number, [percent] from @.t where type = 'CLOR') a full outer join
> (select number, [percent] from @.t where type = 'MTOR') b on a.number =
> b.number
> order by number
> "deodev" wrote:
>|||Should work just fine.. now if you had 150 different COLUMN_NAME's in
your table (ie in my example) that might be a problem. since the select
statement pretty much builds a long string'd dynamic query using
ryans's way of creating the columns. hmm whats the maximum length of a
nvarchar(max) ? I've seem to have forgotten. :P|||The code from Ryan works -
I tried the code from P - have some syntax problems.
Thanks
a lot - greatly appreciated
Deo.
deodev
"jebuskrust@.gmail.com" wrote:

> Should work just fine.. now if you had 150 different COLUMN_NAME's in
> your table (ie in my example) that might be a problem. since the select
> statement pretty much builds a long string'd dynamic query using
> ryans's way of creating the columns. hmm whats the maximum length of a
> nvarchar(max) ? I've seem to have forgotten. :P
>|||That why we created Rac:)
www.rac4sql.net
<jebuskrust@.gmail.com> wrote in message
news:1145553907.545097.230810@.u72g2000cwu.googlegroups.com...
> Pain in the kiester :(
>|||You may find the PIVOT keyword helpful.
http://msdn2.microsoft.com/en-us/library/ms177410(SQL.90).aspx