CODEWITHSUNDEEP
sqlsql-server
Anup
Anup

Reputation: 9738

How do I select an extra row for each row in the result set in 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:

Enter image description here

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

Enter image description here

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

Upvotes: 16

Views: 3542

Answers (8)

user184411
user184411

Reputation: 37

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

Upvotes: 1

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

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.

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.

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

Upvotes: 24

sagi
sagi

Reputation: 40481

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:

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

Upvotes: 12

Shushil Bohara
Shushil Bohara

Reputation: 5656

We can use CONCAT also instead of + for SQL2008+ 

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

Upvotes: 1

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

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

Upvotes: 6

TT.
TT.

Reputation: 16137

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;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269673

If you just want carriage returns and "GO" after each line, you can put that in the 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.

Upvotes: 12

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

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)

Upvotes: 4

Related Questions