sql根据数据建立临时表

[kanmaogou]

已知表数据:
name tv types
--------------------
a 1 rest
a 2 work
a 3 rest
a 4 work
a 5 rest
a 6 work
a 7 rest
b 1 rest
b 2 work
b 3 rest
c 1 work

希望建立表A,字段根据types建立,如a有7条语句，建立7个字段，并根据types类型填充数据，如下
name t1 t2 t3 t4 t5 t6 t7
-------------------------------
a 1 2 3 4 5 6 7
b 1 2 3
c 1


谢谢大神，搞不出来啊


CREATE TABLE #T
(sname VARCHAR(10),
stv VARCHAR(10),
stype VARCHAR(10))

INSERT INTO #T
SELECT 'A','1','rest' UNION ALL
SELECT 'A','2','work' UNION ALL
SELECT 'A','3','rest' UNION ALL
SELECT 'A','4','work' UNION ALL
SELECT 'A','5','rest' UNION ALL
SELECT 'A','6','work' UNION ALL
SELECT 'A','7','rest' UNION ALL
SELECT 'B','1','rest' UNION ALL
SELECT 'B','2','work' UNION ALL
SELECT 'B','3','rest' UNION ALL
SELECT 'C','1','work'

[RINK_1]

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(NAME VARCHAR(10),
 TV INT,
 TYPES VARCHAR(10))

INSERT INTO #T
SELECT 'A',1,'REST' UNION ALL
SELECT 'A',2,'WORK' UNION ALL
SELECT 'A',3,'REST' UNION ALL
SELECT 'A',4,'WORK' UNION ALL
SELECT 'A',5,'REST' UNION ALL
SELECT 'A',6,'WORK' UNION ALL
SELECT 'A',7,'REST' UNION ALL
SELECT 'B',1,'REST' UNION ALL
SELECT 'B',2,'WORK' UNION ALL
SELECT 'B',3,'REST' UNION ALL
SELECT 'C',1,'WORK' UNION ALL
SELECT 'C',2,'REST' UNION ALL
SELECT 'C',3,'REST'



WITH CTE_1
AS
(SELECT *,
ROW_NUMBER() OVER (PARTITION BY NAME,TYPES ORDER BY TV) AS RN_1
FROM #T),

CTE_2
AS
(SELECT *,
CASE WHEN TYPES='REST' THEN (RN_1-1)*2+1 ELSE RN_1*2 END AS RN_2
FROM CTE_1)

SELECT * INTO #A FROM CTE_2 

DECLARE @SQL VARCHAR(8000)

SELECT 
@SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN RN_2='+CAST(RN_2 AS VARCHAR)+' THEN CAST(TV AS VARCHAR) ELSE '''' END) AS T'+CAST(RN_2 AS VARCHAR)
FROM
(SELECT DISTINCT RN_2 FROM #A) AS A ORDER BY RN_2

SET @SQL='SELECT NAME, '+@SQL+' FROM #A GROUP BY NAME'

EXEC(@SQL)

DROP TABLE #A

[leo_lesley]

--   看你的结果，应该是简单的行列转换就可以了
CREATE TABLE #T
(sname VARCHAR(10),
 stv VARCHAR(10),
 stype VARCHAR(10))

INSERT INTO #T
SELECT 'A','1','rest' UNION ALL
SELECT 'A','2','work' UNION ALL
SELECT 'A','3','rest' UNION ALL
SELECT 'A','4','work' UNION ALL
SELECT 'A','5','rest' UNION ALL
SELECT 'A','6','work' UNION ALL
SELECT 'A','7','rest' UNION ALL
SELECT 'B','1','rest' UNION ALL
SELECT 'B','2','work' UNION ALL
SELECT 'B','3','rest' UNION ALL
SELECT 'C','1','work' 


DECLARE @s VARCHAR(1000) ,  @sql VARCHAR(2000)
-- 行列转换就可以了
SELECT @s = ISNULL(@s,'') + ',[' + stv + ']' FROM #T GROUP BY stv
SELECT @sql = ' SELECT *  FROM (SELECT sname,stv FROM #T) a PIVOT(MAX(stv) FOR stv IN( ' + stuff(@s,1,1,'') + ' )) t '

EXEC (@sql)


DROP TABLE #T

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([name] nvarchar(21),[tv] int,[types] nvarchar(24))
Insert #T
select N'a',1,N'rest' union all
select N'a',2,N'work' union all
select N'a',3,N'rest' union all
select N'a',4,N'work' union all
select N'a',5,N'rest' union all
select N'a',6,N'work' union all
select N'a',7,N'rest' union all
select N'b',1,N'rest' union all
select N'b',2,N'work' union all
select N'b',3,N'rest' union all
select N'c',1,N'work'
Go
--测试数据结束
DECLARE @sql VARCHAR(MAX)
SET @sql = 'select Name'
SELECT  @sql = @sql + ',max(case tv when ''' + RTRIM(tv)
        + ''' then [tv] else '''' end)[t' + RTRIM(tv) + ']'
FROM    ( SELECT DISTINCT
                    tv
          FROM      #T
        ) a
SET @sql = @sql
    + ' from #T group by Name'
EXEC(@sql)

[kanmaogou]

不好意思，各位大神，我表述的有问题 name tv types svalue ------------------------- a 1 rest a1 a 2 work a2 a 3 rest a3 a 4 work a4 a 5 rest a5 a 6 work a6 a 7 rest a7 b 1 rest b1 b 2 work b2 b 3 rest b3 c 1 work c1 希望得到如下数据： name t1 t2 t3 t4 t5 t6 t7 ------------------------------- a a1 a2 a3 a4 a5 a6 a7 b b1 b2 b3 c c1

[kanmaogou]

引用 1 楼 RINK_1 的回复:

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(NAME VARCHAR(10),
 TV INT,
 TYPES VARCHAR(10))

INSERT INTO #T
SELECT 'A',1,'REST' UNION ALL
SELECT 'A',2,'WORK' UNION ALL
SELECT 'A',3,'REST' UNION ALL
SELECT 'A',4,'WORK' UNION ALL
SELECT 'A',5,'REST' UNION ALL
SELECT 'A',6,'WORK' UNION ALL
SELECT 'A',7,'REST' UNION ALL
SELECT 'B',1,'REST' UNION ALL
SELECT 'B',2,'WORK' UNION ALL
SELECT 'B',3,'REST' UNION ALL
SELECT 'C',1,'WORK' UNION ALL
SELECT 'C',2,'REST' UNION ALL
SELECT 'C',3,'REST'



WITH CTE_1
AS
(SELECT *,
ROW_NUMBER() OVER (PARTITION BY NAME,TYPES ORDER BY TV) AS RN_1
FROM #T),

CTE_2
AS
(SELECT *,
CASE WHEN TYPES='REST' THEN (RN_1-1)*2+1 ELSE RN_1*2 END AS RN_2
FROM CTE_1)

SELECT * INTO #A FROM CTE_2 

DECLARE @SQL VARCHAR(8000)

SELECT 
@SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN RN_2='+CAST(RN_2 AS VARCHAR)+' THEN CAST(TV AS VARCHAR) ELSE '''' END) AS T'+CAST(RN_2 AS VARCHAR)
FROM
(SELECT DISTINCT RN_2 FROM #A) AS A ORDER BY RN_2

SET @SQL='SELECT NAME, '+@SQL+' FROM #A GROUP BY NAME'

EXEC(@SQL)

DROP TABLE #A

大佬谢谢，其实你最懂我，我的表述有点问题，再开一贴，等你

[kanmaogou]

引用 2 楼 leo_lesley 的回复:

--   看你的结果，应该是简单的行列转换就可以了
CREATE TABLE #T
(sname VARCHAR(10),
 stv VARCHAR(10),
 stype VARCHAR(10))

INSERT INTO #T
SELECT 'A','1','rest' UNION ALL
SELECT 'A','2','work' UNION ALL
SELECT 'A','3','rest' UNION ALL
SELECT 'A','4','work' UNION ALL
SELECT 'A','5','rest' UNION ALL
SELECT 'A','6','work' UNION ALL
SELECT 'A','7','rest' UNION ALL
SELECT 'B','1','rest' UNION ALL
SELECT 'B','2','work' UNION ALL
SELECT 'B','3','rest' UNION ALL
SELECT 'C','1','work' 


DECLARE @s VARCHAR(1000) ,  @sql VARCHAR(2000)
-- 行列转换就可以了
SELECT @s = ISNULL(@s,'') + ',[' + stv + ']' FROM #T GROUP BY stv
SELECT @sql = ' SELECT *  FROM (SELECT sname,stv FROM #T) a PIVOT(MAX(stv) FOR stv IN( ' + stuff(@s,1,1,'') + ' )) t '

EXEC (@sql)


DROP TABLE #T

大佬谢谢，我的表述有点问题，再开一贴，等你