Crosstab, Rotate, Pivot
Shameless promotion, that's what the title is all about: Include all the keywordsanyone might use when asking this question:
Question: How do I rotate a table so that different row values in a single column become different columns in a new table?
Answer: I could have sworn I talked about this years ago, but apparently not... at least not anywhere people can find it.
Is that a subtle dig at the NNTP newsgroups? Yes, I'm sorry, it is... I'll try to be less subtle: NNTP sucks, the days of the newsgroups are numbered, and the future for SQL Anywhere questions and answers lies withSQLA! <g>
SQL Anywhere doesn't have the ability to rotate (crosstab, pivot, whatever) a table, and that feature isn't coming any time soon. What it does have is EXECUTE IMMEDIATE, plus the ability to code IF expressions just about anywhere in the SELECT statement. You can combine those two features to kludge a solution.
Is "kludge" too strong a word? Maybe so, since performance is pretty good. Good enough for this technique to be used in real time by the Foxhound database monitor when analyzing data gathered from thousands of database connections. But, no question... the code's funky.
It's actually hard to explain what rotating a table is all about, it's not exactly as simple as turning rows into columns and vice versa... it's easier to use an example.
Here's a table showing sales by state and quarter:
-- Part 1: Initialize data.
BEGIN
DROP TABLE t1;
EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE t1 (
c1 VARCHAR ( 10 ) NOT NULL,
c2 VARCHAR ( 10 ) NOT NULL,
c3 INTEGER NOT NULL,
PRIMARY KEY ( c1, c2 ) );
INSERT t1 VALUES ( 'CA', 'Q1', 1000 );
INSERT t1 VALUES ( 'CA', 'Q2', 2000 );
INSERT t1 VALUES ( 'CA', 'Q3', 9000 );
INSERT t1 VALUES ( 'CA', 'Q4', 7000 );
INSERT t1 VALUES ( 'NY', 'Q1', 4000 );
INSERT t1 VALUES ( 'NY', 'Q2', 5000 );
INSERT t1 VALUES ( 'NY', 'Q3', 1000 );
INSERT t1 VALUES ( 'NY', 'Q4', 6000 );
INSERT t1 VALUES ( 'FL', 'Q1', 9000 );
INSERT t1 VALUES ( 'FL', 'Q2', 7000 );
INSERT t1 VALUES ( 'FL', 'Q3', 2000 );
INSERT t1 VALUES ( 'FL', 'Q4', 1000 );
INSERT t1 VALUES ( 'AZ', 'Q1', 5000 );
INSERT t1 VALUES ( 'AZ', 'Q2', 5000 );
INSERT t1 VALUES ( 'AZ', 'Q3', 1000 );
INSERT t1 VALUES ( 'AZ', 'Q4', 3000 );
INSERT t1 VALUES ( 'MA', 'Q1', 2000 );
INSERT t1 VALUES ( 'MA', 'Q2', 6000 );
INSERT t1 VALUES ( 'MA', 'Q3', 5000 );
INSERT t1 VALUES ( 'MA', 'Q4', 3000 );
COMMIT;
SELECT * FROM t1 ORDER BY c1, c2;
So far, so good... the table is nicely normalized, everything's elegant... and useless:
c1 c2 c3
AZ Q1 5000
AZ Q2 5000
AZ Q3 1000
AZ Q4 3000
CA Q1 1000
CA Q2 2000
CA Q3 9000
CA Q4 7000
FL Q1 9000
FL Q2 7000
FL Q3 2000
FL Q4 1000
MA Q1 2000
MA Q2 6000
MA Q3 5000
MA Q4 3000
NY Q1 4000
NY Q2 5000
NY Q3 1000
NY Q4 6000
What folks often want to see is something like this, sales by quarter for each state:
c2 AZ CA FL MA NY
Q1 5000 1000 9000 2000 4000
Q2 5000 2000 7000 6000 5000
Q3 1000 9000 2000 5000 1000
Q4 3000 7000 1000 3000 6000
Here's how you can do that in SQL Anywhere:
-- Part 2: Pivot c1 values into columns.
BEGIN
DECLARE @sql LONG VARCHAR;
SET @sql = 'SELECT c2';
FOR f_fetch
AS c_fetch NO SCROLL CURSOR FOR
SELECT DISTINCT t1.c1 AS @c1
FROM t1
ORDER BY t1.c1
FOR READ ONLY
DO
SET @sql = STRING (
@sql,
', SUM ( ( IF t1.c1 = ''',
@c1,
''' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "',
@c1,
'"' );
END FOR;
SET @sql = STRING (
@sql,
' INTO #t1 FROM t1 GROUP BY c2' );
MESSAGE @sql TO CONSOLE;
EXECUTE IMMEDIATE @sql;
SELECT * FROM #t1 ORDER BY c2; -- pivot table
END;
What if you want to see sales by state for each quarter?
c1 Q1 Q2 Q3 Q4
AZ 5000 5000 1000 3000
CA 1000 2000 9000 7000
FL 9000 7000 2000 1000
MA 2000 6000 5000 3000
NY 4000 5000 1000 6000
Here's the code for that:
-- Part 3: Pivot c2 values into columns.
BEGIN
DECLARE @sql LONG VARCHAR;
SET @sql = 'SELECT c1';
FOR f_fetch
AS c_fetch NO SCROLL CURSOR FOR
SELECT DISTINCT t1.c2 AS @c2
FROM t1
ORDER BY t1.c2
FOR READ ONLY
DO
SET @sql = STRING (
@sql,
', SUM ( ( IF t1.c2 = ''',
@c2,
''' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "',
@c2,
'"' );
END FOR;
SET @sql = STRING (
@sql,
' INTO #t1 FROM t1 GROUP BY c1' );
MESSAGE @sql TO CONSOLE;
EXECUTE IMMEDIATE @sql;
SELECT * FROM #t1 ORDER BY c1; -- pivot table
END;
Here's where the magic lies, in the SUMs of row values multiplied by 1 or 0; the following SELECT statements are generated by the code shown above:
SELECT c2,
SUM ( ( IF t1.c1 = 'AZ' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "AZ",
SUM ( ( IF t1.c1 = 'CA' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "CA",
SUM ( ( IF t1.c1 = 'FL' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "FL",
SUM ( ( IF t1.c1 = 'MA' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "MA",
SUM ( ( IF t1.c1 = 'NY' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "NY"
INTO #t1
FROM t1
GROUP BY c2
SELECT c1,
SUM ( ( IF t1.c2 = 'Q1' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "Q1",
SUM ( ( IF t1.c2 = 'Q2' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "Q2",
SUM ( ( IF t1.c2 = 'Q3' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "Q3",
SUM ( ( IF t1.c2 = 'Q4' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "Q4"
INTO #t1
FROM t1
GROUP BY c1
The "SUM IF 1 OR 0" trick is an old one, used by generations of programmers to write funky code for icky problems, and not just in SQL. In fact, the code shown above is really simple compared with what goes on in the world, just a starting point.
If folks are interested I've got a couple of other demos that build on this technique... not the raw incomprehensible code you find in real-world applications, but hammered down into the basics for presentation... that's actually harder to do, the hammering down, harder than writing the original incomprehensible stuff :)
_____________________________________ SQLserver 舉例交叉寫法字元串
select * from sysobjects where [xtype]='u'
go
if exists(select id from sysobjects where name='work_order_wo')
drop table work_order_wo--删除與實驗沖突的表
go
create table work_order_wo--建立工序表
(
[id] int identity(1,1),
[name] nvarchar(20) not null,
subject nvarchar(20) not null,
order nvarchar(20) not null
)
go
select * from work_order_wo
go
create table work_order_wo--建立工序表
(
[id] int identity(1,1),
[name] nvarchar(20) not null,
name1 nvarchar(20) not null,
name2 nvarchar(20) not null
)
insert work_order_wo values ('張三','工序1','打磨');
insert work_order_wo values ('張三','工序2','打磨2');
insert work_order_wo values ('張三','工序3','打磨3');
insert work_order_wo values ('李四','工序1','沖壓');
insert work_order_wo values ('李四','工序2','沖壓2');
insert work_order_wo values ('王五','工序1','沖壓2');
declare @sql varchar(8000)
set @sql='select name as ' + '姓名'
select @sql [email protected] + ' ,max(case name1 when ''' + name1 + ''' then name2 end) [' +name1+ ']'
from (select distinct name1 from work_order_wo) as a
set @[email protected] + ' from work_order_wo group by name'
print @sql
exec(@sql)
------------------------------------------------------- 引用SQL但不支援sybase
SQL 行列轉換，sql2000和2005--多行轉一行
版權聲明：轉載時請以超連結形式标明文章原始出處和作者資訊及本聲明
http://jiang5311.blogbus.com/logs/53228755.html
以下主講sql多行轉為一列的合并問題，并在sql2000和2005得到驗證，希望大家收藏！
描述：将如下形式的資料按id字段合并value字段。
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到結果：
id value
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即：group by id, 求 value 的和（字元串相加）
*/
--1、sql2000中隻能用自定義的函數解決
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
create function dbo.f_str(@id int) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + cast(value as varchar) from tb where id = @id
set @str = right(@str , len(@str) - 1)
return @str
end
go
--調用函數
select id , value = dbo.f_str(id) from tb group by id
drop function dbo.f_str
drop table tb
--2、sql2005中的方法
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
select id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')
from tb
group by id
drop table tb
--3、使用遊标合并資料
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
declare @t table(id int,value varchar(100))--定義結果集表變量
--定義遊标并進行合并處理
declare my_cursor cursor local for
select id , value from tb
declare @id_old int , @id int , @value varchar(10) , @s varchar(100)
open my_cursor
fetch my_cursor into @id , @value
select @id_old = @id , @s=''
while @@FETCH_STATUS = 0
begin
if @id = @id_old
select @s = @s + ',' + cast(@value as varchar)
else
begin
insert @t values(@id_old , stuff(@s,1,1,''))
select @s = ',' + cast(@value as varchar) , @id_old = @id
end
fetch my_cursor into @id , @value
END
insert @t values(@id_old , stuff(@s,1,1,''))
close my_cursor
deallocate my_cursor
select * from @t
drop table tb
--1. 建立表，添加測試資料
CREATE TABLE tb(id int, [value] varchar(10))
INSERT tb SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'
--SELECT * FROM tb
--2 在SQL2000隻能用自定義函數實作
----2.1 建立合并函數fn_strSum，根據id合并value值
GO
CREATE FUNCTION dbo.fn_strSum(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @values varchar(8000)
SET @values = ''
SELECT @values = @values + ',' + value FROM tb WHERE
RETURN STUFF(@values, 1, 1, '')
END
GO
-- 調用函數
SELECT id, VALUE = dbo.fn_strSum(id) FROM tb GROUP BY id
DROP FUNCTION dbo.fn_strSum
----2.2 建立合并函數fn_strSum2，根據id合并value值
GO
CREATE FUNCTION dbo.fn_strSum2(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @values varchar(8000)
SELECT @values = isnull(@values + ',', '') + value FROM tb WHERE
RETURN @values
END
GO
-- 調用函數
SELECT id, VALUE = dbo.fn_strSum2(id) FROM tb GROUP BY id
DROP FUNCTION dbo.fn_strSum2
--3 在SQL2005中的新解法
----3.1 使用OUTER APPLY
SELECT *
FROM (SELECT DISTINCT id FROM tb) A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
----3.2 使用XML
SELECT id, [values]=STUFF((SELECT ','+[value] FROM tb t WHERE id=tb.id FOR XML PATH('')), 1, 1, '')
FROM tb
GROUP BY id
--4 删除測試表tb
drop table tb
--交叉表利用變量
create table work_order_wo--建立工序表
(
[id] int identity(1,1),
[name] nvarchar(20) not null,
name1 nvarchar(20) not null,
name2 nvarchar(20) not null
)
insert work_order_wo values ('張三','工序1','打磨');
insert work_order_wo values ('張三','工序2','打磨2');
insert work_order_wo values ('張三','工序3','打磨3');
insert work_order_wo values ('李四','工序1','沖壓');
insert work_order_wo values ('李四','工序2','沖壓2');
insert work_order_wo values ('王五','工序1','沖壓2');
declare @sql varchar(8000)
set @sql='select name as ' + '姓名'
select @sql [email protected] + ' ,max(case name1 when ''' + name1 + ''' then name2 end) [' +name1+ ']'
from (select distinct name1 from work_order_wo) as a
set @[email protected] + ' from work_order_wo group by name'
print @sql
exec(@sql)
--拼字元串在一起
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
select id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')
from tb
group by id
create table work_order_wo--建立工序表
(
[id] int identity(1,1),
[name] nvarchar(20) not null,
name1 nvarchar(20) not null,
name2 nvarchar(20) not null
)
insert work_order_wo values ('張三','工序1','打磨');
insert work_order_wo values ('張三','工序2','打磨2');
insert work_order_wo values ('張三','工序3','打磨3');
insert work_order_wo values ('李四','工序1','沖壓');
insert work_order_wo values ('李四','工序2','沖壓2');
insert work_order_wo values ('王五','工序1','沖壓2');
declare @sql varchar(8000)
set @sql='select name as ' + '姓名'
select @sql [email protected] + ' ,max(case name1 when ''' + name1 + ''' then name2 end) [' +name1+ ']'
from (select distinct name1 from work_order_wo) as a
set @[email protected] + ' from work_order_wo group by name'
print @sql
exec(@sql)
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)