I need a query that will sort data from Tab and put it into NewTab, which looks like this:
我需要一个查询,它将从Tab中对数据进行排序并将其放入NewTab,如下所示:
I'm using SQL Server 2008 R2 Express, and I've tried code:
我正在使用SQL Server 2008 R2 Express,我尝试过代码:
INSERT INTO NewTab(Var1,Var2,Var3,DTime) VALUES (
(SELECT Value FROM Tab WHERE VarName='Var1'),
(SELECT Value FROM Tab WHERE VarName='Var2'),
(SELECT Value FROM Tab WHERE VarName='Var3'),
(SELECT DTime FROM Tab WHERE VarName='Var1')
)
But server is returning and error:
但服务器正在返回并出错:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
子查询返回的值超过1。当子查询遵循=,!=,<,<=,>,> =或子查询用作表达式时,不允许这样做。
I'm quite new to SQL and I'll be grateful if someone help me with that.
我对SQL很陌生,如果有人帮助我,我将不胜感激。
2 个解决方案
#1
4
You seems to be trying to pivot the data. There are other ways to do it (i.e using PIVOT operator), but I guess that using old-school aggregate functions help us get a better idea of how data is processed:
您似乎试图转动数据。还有其他方法可以做到这一点(即使用PIVOT运算符),但我想使用旧式聚合函数可以帮助我们更好地了解数据的处理方式:
SELECT DTime
, SUM(CASE WHEN varname = 'Var1' THEN value ELSE NULL END) AS [Var1]
, SUM(CASE WHEN varname = 'Var2' THEN value ELSE NULL END) AS [Var2]
, SUM(CASE WHEN varname = 'Var3' THEN value ELSE NULL END) AS [Var3]
FROM Tab
GROUP BY DTime
#2
3
Try this !!
尝试这个 !!
you could use a pivot operator
你可以使用枢轴运算符
select * into newtab from [dbo].[Tab]
pivot(
max(value)
for varname in([var1],[var2],[var3])
)as piv;
#1
4
You seems to be trying to pivot the data. There are other ways to do it (i.e using PIVOT operator), but I guess that using old-school aggregate functions help us get a better idea of how data is processed:
您似乎试图转动数据。还有其他方法可以做到这一点(即使用PIVOT运算符),但我想使用旧式聚合函数可以帮助我们更好地了解数据的处理方式:
SELECT DTime
, SUM(CASE WHEN varname = 'Var1' THEN value ELSE NULL END) AS [Var1]
, SUM(CASE WHEN varname = 'Var2' THEN value ELSE NULL END) AS [Var2]
, SUM(CASE WHEN varname = 'Var3' THEN value ELSE NULL END) AS [Var3]
FROM Tab
GROUP BY DTime
#2
3
Try this !!
尝试这个 !!
you could use a pivot operator
你可以使用枢轴运算符
select * into newtab from [dbo].[Tab]
pivot(
max(value)
for varname in([var1],[var2],[var3])
)as piv;