求多行转成多列的实例的sql语句,该如何解决
求多行转成多列的实例的sql语句
table t
id price
1 2
1 1
2 0.1
2 0.3
2 0.4
id price1 price2 price3
1 2 1 NUll
2 0.1 0.3 0.4
想要用动态语句实现,因为ID号不止这两个,同时同一个ID号可能有更多的记录,谢谢各位赐教!!
------解决方案--------------------
table t
id price
1 2
1 1
2 0.1
2 0.3
2 0.4
id price1 price2 price3
1 2 1 NUll
2 0.1 0.3 0.4
想要用动态语句实现,因为ID号不止这两个,同时同一个ID号可能有更多的记录,谢谢各位赐教!!
------解决方案--------------------
- SQL code
select *,row_number() over (partition by id order by getdate()) as rnt into tp from tb declare @sql varchar(max) set @sql = 'select id' select @sql = @sql + ',max(case when rnt='+ltrim(rnt)+' then price else null end) [price'+ltrim(rnt)+']' from(select distinct rnt from tp) select @sql = @sql + ' from tp group by id' exec(@sql) drop table tp
------解决方案--------------------
- SQL code
IF OBJECT_id(N'FENG') IS NOT NULL DROP TABLE FENG GO CREATE TABLE FENG(id int,price VARCHAR(10)) INSERT FENG SELECT 1,2 UNION ALL SELECT 1,1 UNION ALL SELECT 2,0.1 UNION ALL SELECT 2,0.3 UNION ALL SELECT 2,0.4 GO IF OBJECT_ID('tempdb..#LSB') IS NOT NULL DROP TABLE #LSB GO SELECT ROW = ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT 0)) ,* INTO #LSB FROM FENG DECLARE @XK VARCHAR(50) ;WITH AA AS (SELECT DISTINCT ROW FROM #LSB ) SELECT @XK = ISNULL(@XK+',','')+QUOTENAME(ROW) FROM AA EXEC ('SELECT id,'+@XK+' FROM #LSB PIVOT(MAX(price) FOR ROW IN ('+@XK+')) PIV ORDER BY 1') /* id 1 2 3 1 2.0 1.0 NULL 2 0.1 0.3 0.4 */
------解决方案--------------------
如果非常多的话 把@XK 定义大一些 8000内
------解决方案--------------------