如何在SQL中为结果集中的每一行选择一个额外的行?

I have the below query:

我有以下查询:

SELECT '['+name+']' FROM sys.schemas
Where name <> 'dbo' AND name <> 'guest' AND name <> 'INFORMATION_SCHEMA'
....

The result of this query is simple:

这个查询的结果很简单:

I need an extra row with text 'GO' after every record like this:

我需要在每条记录后加一行文字“GO”:

Is there a way to do this? I tried with a foreach statement, but I was unable to replicate the desired results.

有没有办法做到这一点?我尝试使用foreach语句，但无法复制所需的结果。

8 个解决方案

#1

If you just want carriage returns and "GO" after each line, you can put that in the SELECT:

如果您只想要回车，并在每行后“GO”，您可以将其放入SELECT:

SELECT '[' + name + ']
GO'
FROM sys.schemas 
Where name <> 'dbo' AND name <> 'guest' AND name <> 'INFORMATION_SCHEMA' 
....

This is not exactly what you are asking for, but it might do what you need.

这并不是你想要的，但它可能会满足你的需要。

#2

Just add Go statement to the Dynamic code by hitting ENTER key and see the result pasting in SSMS. This will populate with GO statement in Next line.

只需在动态代码中添加Go语句，点击ENTER键，就可以在SSMS中看到结果粘贴。这将在下一行中用GO语句填充。

You should also use QUOTENAME rather than concatenating the square brackets yourself (to work correctly for names containing ]) and the where clause can be simplified with NOT IN.

您还应该使用QUOTENAME，而不是自己连接方括号(以便正确处理包含的名称)，where子句可以简化为NOT IN。

SELECT QUOTENAME(name) +'
GO
' 
FROM sys.schemas 
WHERE name NOT IN  ('dbo', 'guest', 'INFORMATION_SCHEMA')

#3

You can do it by using UNION ALL to populate these extra values, and the use CASE EXPRESSION in the ORDER BY clause for this specific order:

您可以通过使用UNION ALL来填充这些额外的值，以及这个特定顺序的ORDER by子句中的用例表达式:

SELECT t.name_col
FROM(
    SELECT '['+name+']' as name_col,'['+name+']' as ord_col
    FROM sys.schemas 
    Where name NOT IN('dbo','guest','INFORMATION_SCHEMA')
    UNION ALL 
    SELECT 'GO','['+name+']' as ord_col 
    FROM sys.schemas 
    Where name NOT IN('dbo','guest','INFORMATION_SCHEMA')) t
ORDER BY t.ord_col,
         CASE WHEN t.name_col = 'GO' THEN 1 ELSE 0 END

#4

select  c.txt
from    sys.schemas cross apply (values ('['+name+']'),('GO')) c(txt)
where   name not in ('dbo','guest','INFORMATION_SCHEMA')

Guaranteed order

保证订单

select      c.txt
from        sys.schemas s cross apply (values ('['+name+']',1),('GO',2)) c(txt,ord)
where       s.name not in ('dbo','guest','INFORMATION_SCHEMA') 
order by    s.name,c.ord

#5

Here is another way with cross apply values

这里是另一种使用交叉应用值的方法。

SELECT B.*
FROM   (SELECT '[' + NAME + ']' AS NAME1,
               'GO'             AS GO1
        FROM   SYS.SCHEMAS
        WHERE  NAME NOT IN ( 'DBO', 'GUEST', 'INFORMATION_SCHEMA' ))A
       CROSS APPLY ( VALUES(NAME1),
                           (GO1) ) B(NAME1)

#6

Newline explicitely inside the column:

在列内显式地行:

SELECT QUOTENAME(name)+CHAR(10)+CHAR(13)+'GO' 
FROM sys.schemas 
WHERE name <> 'dbo' AND name <> 'guest' AND name <> 'INFORMATION_SCHEMA';

Newline in a new row:

新行中的新行:

SELECT
    lot
FROM
    (
        SELECT name=CAST(QUOTENAME(name) AS VARCHAR(256)),extra_line=CAST('GO' AS VARCHAR(256))
        FROM sys.schemas
        WHERE name <> 'dbo' AND name <> 'guest' AND name <> 'INFORMATION_SCHEMA'
    ) as p
    UNPIVOT(lot FOR l IN (name,extra_line)) AS up;

#7

We can use CONCAT also instead of + for SQL2008+

我们也可以用CONCAT代替+作为SQL2008+

SELECT CONCAT('[', name ,'] GO ')
FROM sys.schemas 
WHERE name <> 'dbo' AND name <> 'guest' AND name <> 'INFORMATION_SCHEMA';

#8

How about something like:

如何像:

select name from (
SELECT 2 * rowid as r, 
    '['+name+']' as name
    FROM sys.schemas 
union
select 2 * rowid + 1, 'GO' FROM sys.schemas 
)s
order by r

#1