在sqlserver中使用unpivot [英] Usage of unpivot in sqlserver
本文介绍了在sqlserver中使用unpivot的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我想连续找出最大值。因此,我使用unpivot找出最大值,但它给出了一个错误。谁能帮帮我吗。在此先感谢。
创建 表 MAXVALUE
(
ID INT IDENTITY ,
A INT ,
B INT ,
C INT
)
这是myquery:
SELECT MAX(VAL) AS MAXVALUE_ROW FROM
( SELECT * FROM MAXVALUE UNPIVOT
(Id for [max(val)] IN
([A],[B],[C] )
))
错误:
消息102,级别15,状态1,行5
'')''附近的语法不正确。
决方案
很抱歉,我没有时间为你的帖子提供解决方案,我有一个示例代码。
请看看这个
UNPIVOT:
-------
用于将列转换为行
创建 表 tbl_stdmarksdata (studentname nvarchar ( 100 ),我 int ,II int ,III int ,IV int ,V int )
go
插入 到 tbl_stdmarksdata(studentname,I,II,III,IV,V)
选择 ' Vivek Johari ', 30 , 20 , 35 , 40 , 45
Union 全部
选择 ' Chandra Singh', 30 , 20 , 35 , 44 , 80
联盟 全部
选择 ' Avinash Dubey', 30 , 25 , 35 , 20 , 39
联盟 全部
选择 ' Pankaj Kumar', 33 , 29 , 30 , 60 , 50
go
选择 * 来自 tbl_stdmarksdata
go
选择 studentname,Marks,Grade
来自 tbl_stdmarksdata
unpivot
(标记 等级 in (I,II,III,IV,V)) as tblunpvt
试试这个...
选择 id,COL,VAL 来自
(
SELECT Row_Number() over ( order by val desc ) as srno,id,COL,VAL
FROM
( SELECT * FROM MAXVALUE) as p
UNPIVOT
(VAL COL IN ([A],[B],[C]))< span class =code-keyword> as b
) as t 其中 srno = 1
快乐编码!
:)
Hi all,
I want to find out maximum value in a row. So, Iam using unpivot to find out the max value but it is giving an error. Can any one please help me. Thanks in advance.
CREATE TABLE MAXVALUE
(
ID INT IDENTITY,
A INT,
B INT,
C INT
)
this is myquery :
SELECT MAX(VAL) AS MAXVALUE_ROW FROM
(SELECT *FROM MAXVALUE UNPIVOT
(Id for [max(val)] IN
([A],[B],[C])
))
Error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '')''.
解决方案
hi , sorry to say i don''t have time to give solution for your post , i have one sample code .
please have a look on this
UNPIVOT:
-------
Used to convert columns into rows
Create table tbl_stdmarksdata (studentname nvarchar(100), I int, II int, III int, IV int, V int) go Insert into tbl_stdmarksdata (studentname,I,II,III,IV,V) Select 'Vivek Johari',30,20,35, 40, 45 Union All Select 'Chandra Singh',30,20,35,44, 80 Union All Select 'Avinash Dubey',30,25,35,20, 39 Union All Select 'Pankaj Kumar',33,29,30, 60, 50 go select * from tbl_stdmarksdata go select studentname,Marks,Grade from tbl_stdmarksdata unpivot (Marks for Grade in (I,II,III,IV,V) ) as tblunpvt
try this...
select id,COL,VAL from ( SELECT Row_Number() over(order by val desc) as srno, id,COL,VAL FROM (SELECT * FROM MAXVALUE ) as p UNPIVOT (VAL for COL IN ([A],[B],[C])) as b ) as t where srno=1
Happy Coding!
:)
这篇关于在sqlserver中使用unpivot的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文