INSERT INTO查询中的多个SELECT

时间:2021-08-09 00:52:13

I need a query that will sort data from Tab and put it into NewTab, which looks like this:

我需要一个查询,它将从Tab中对数据进行排序并将其放入NewTab,如下所示:

INSERT INTO查询中的多个SELECT

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;