CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[OrderID] [int] NULL,
[VoteResult] [nchar](10) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into Test
select 1,1,A
union select 1,2,B
union select 1,3,C
union select 2,1,A
union select 2,2,B
union select 2,3,B
union select 2,4,A想得到的效果是
UserID 1 2 3 4...(动态生成,因为有好多,不可能一个个列出来)
由于SQL论坛中行列转化都是考试成绩那个,分数是int型,可以取出max,而本处是字符串类型,我行列转化出好多null,不知道怎么解决!不知道描述清楚了吗?谢谢大家了啊,sql2000和sql2005的语句都可以的啊
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[OrderID] [int] NULL,
[VoteResult] [nchar](10) NULL,
) ON [PRIMARY]
insert into Test
select 1,1,'A'
union select 1,2,'B'
union select 1,3,'C'
union select 2,1,'A'
union select 2,2,'B'
union select 2,3,'B'
union select 2,4,'A'
godeclare @sql varchar(8000)
set @sql='select VoteResult'select @sql=@sql+',['+rtrim(UserID)+']=max(case UserID when '+rtrim(UserID)+' then 1 else 0 end)'
from (select distinct UserID from Test) tset @sql=@sql+' from Test group by VoteResult'exec(@sql)/*
VoteResult 1 2
---------- ----------- -----------
A 1 1
B 1 1
C 1 0
*/
godrop table test
go
按照上面的测试数据,需要得到下面的结果UserID a1 a2 a3 a4
1 A B A
2 A C A Aa1,a2,a3...有N多呢,是OrderID 那个字段
A,B,C...是VoteResult的结果
A,B,C...是VoteResult的字段的值
OrderID从1开始,有上千个谢谢大家继续一下!!
drop table test
CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[OrderID] [int] NULL,
[VoteResult] [nchar](10) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into Test
select 1,1,'A'
union select 1,2,'B'
union select 1,3,'C'
union select 2,1,'A'
union select 2,2,'B'
union select 2,3,'B'
union select 2,4,'A'
goselect * from test a pivot (max(orderid) for userid in ([1],[2],[3],[4],[5],[6],[7]))b(7 行受影响)
ID VoteResult 1 2 3 4 5 6 7
----------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 A 1 NULL NULL NULL NULL NULL NULL
2 B 2 NULL NULL NULL NULL NULL NULL
3 C 3 NULL NULL NULL NULL NULL NULL
4 A NULL 1 NULL NULL NULL NULL NULL
5 B NULL 2 NULL NULL NULL NULL NULL
6 B NULL 3 NULL NULL NULL NULL NULL
7 A NULL 4 NULL NULL NULL NULL NULL(7
declare @sql varchar(max)
set @sql = 'select UserID '
select @sql = @sql + ' , case orderid when ''' + cast(orderid as nvarchar(4))+ ''' then voteresult else null end [' + cast(orderid as nvarchar(4)) + ']'
from (select distinct orderid from test) as a
set @sql = @sql + ' from test group by UserID,orderid,voteresult'
exec(@sql) 我的sql,但是会生成好多的null,我想着一个UserID就显示一条记录,谢谢大家帮忙
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[OrderID] [int] NULL,
[VoteResult] [nchar](10) NULL,
) ON [PRIMARY]
insert into Test
select 1,1,'A'
union select 1,2,'B'
union select 1,3,'C'
union select 2,1,'A'
union select 2,2,'B'
union select 2,3,'B'
union select 2,4,'A'
godeclare @sql varchar(8000)
set @sql='select UserID'select @sql=@sql+',['+rtrim(OrderID)+']=max(case OrderID when '+rtrim(OrderID)+' then VoteResult else '''' end)'
from (select distinct OrderID from Test) tset @sql=@sql+' from Test group by UserID'exec(@sql)/*
UserID 1 2 3 4
----------- ---------- ---------- ---------- ----------
1 A B C
2 A B B A
*/
godrop table test
go
id name tag
1 a tag1
1 a tag2
1 a tag3
2 b tag2
2 b tag4
3 c tag5 有没有办法得到这样的结果:
id name tag tag tag ....(不定长,或者长度和最长的相等,这里最长的有3个tag)
1 a tag1 tag2 tag3
2 b tag2 tag4 null
3 c tag5 null null ----------------------------你说跟你的不一样???哪点不一样,请问?-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-02 16:32:27
-------------------------------------
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (id INT,name VARCHAR(1),tag VARCHAR(4))
INSERT INTO #T
SELECT 1,'a','tag1' UNION ALL
SELECT 1,'a','tag2' UNION ALL
SELECT 1,'a','tag3' UNION ALL
SELECT 2,'b','tag2' UNION ALL
SELECT 2,'b','tag4' UNION ALL
SELECT 3,'c','tag5'--SQL查询如下:DECLARE @sql VARCHAR(MAX);
SET @sql = '';SELECT
@sql = @sql + ',[tag' + CAST(number + 1 AS VARCHAR) + ']'
FROM master.dbo.spt_values
WHERE type='p'
AND number < (
SELECT TOP 1 rowcnt
FROM (SELECT rowcnt=COUNT(*) OVER(PARTITION BY id)
FROM #T) AS T
);SELECT @sql = STUFF(@sql,1,1,'');EXEC('SELECT *
FROM (SELECT flag=''tag'' + CAST(ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) AS VARCHAR),* FROM #T
) AS A
PIVOT(MAX(tag) FOR flag IN(' + @sql + ')) AS pvt');/*
id name tag1 tag2 tag3
----------- ---- ---- ---- ----
1 a tag1 tag2 tag3
2 b tag2 tag4 NULL
3 c tag5 NULL NULL(3 行受影响)*/
drop table test
go
CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[OrderID] [int] NULL,
[VoteResult] [nchar](10) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into Test
select 1,1,'A'
union select 1,2,'B'
union select 1,3,'C'
union select 2,1,'A'
union select 2,2,'B'
union select 2,3,'B'
union select 2,4,'A'
go
--@s1,@s2可定义多个变量支持上1000列的显示
declare @s1 nvarchar(4000),@s2 nvarchar(4000),@i nvarchar(4),@j int
select @s1='',@s2='',@i=1select @j=max([OrderID]) from Testwhile @i<=@j
select @s1=@s1+case when @i<=50 then ',[a'+@i+']=max(case when [OrderID]='+@i+' then [VoteResult] else '''' end)' else '' end,
@s2=@s2+case when @i between 51 and 100 then ',[a'+@i+']=max(case when [OrderID]='+@i+' then [VoteResult] else '''' end)' else '' end,@i=@i+1exec('select [UserID]'+@s1+@s2+' from Test group by [UserID]')UserID a1 a2 a3 a4
----------- ---------- ---------- ---------- ----------
1 A B C
2 A B B A (2 行受影响)
if object_id('test')is not null
drop table test
CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[OrderID] [int] NULL,
[VoteResult] [nchar](10) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into Test
select 1,1,'A'
union select 1,2,'B'
union select 1,3,'C'
union select 2,1,'A'
union select 2,2,'B'
union select 2,3,'B'
union select 2,4,'A'
go
select * from (select userid,orderid,voteresult from test) a pivot (max(voteresult) for orderid in ([1],[2],[3],[4]))b(7 行受影响)
userid 1 2 3 4
----------- ---------- ---------- ---------- ----------
1 A B C NULL
2 A B B A (2 行受影响)
确实差不多!!