SQL 行转列

ALTER PROCEDURE [dbo].[sp_pro_AnswerReport]
 @HID VARCHAR(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TEMP'))
drop table [#TEMP]


--====创建临时表


CREATE TABLE [dbo].[#TEMP](
--    [tmid] [int] IDENTITY(1,1) NOT NULL,
    [ID] [nvarchar](30) NULL,
    [iid] [nvarchar](30) NULL,
    [oid] [nvarchar](700) NULL)
 
 --=====数据过滤 插入临时表
insert into  #temp
select [sid],iid,oid   from v_SheetOption  where  hid=@HID


--动态生成列,行转列
DECLARE @sql VARCHAR(8000)
DECLARE @COL VARCHAR(100)
SET @sql = 'SELECT row_number() over (order by [ID]) 序号,* from #temp pivot (max(oid) FOR iid IN ('
SELECT @sql = @sql + QUOTENAME(itemno)+',' FROM (select  itemno from MD_SheetTemplateItem where hid=@HID) AS a     
SELECT @sql = LEFT(@sql,LEN(@sql)-1) + ')) a'   


EXEC(@sql)
END