Creating a superset in SQL Server

Question

I have a query that returns a few columns and some information. I want to hardcode a list, and have each row returned each value from my list.

So, currently my SELECT is returning, for example,

ID   Name   Value
1    Mike   404
2    John   404

And lets say, for example, I wish to add a column to my SELECT so that a managers name is also returned with each row. So I have a set of managers, which I want to write myself into the SELECT statement(i.e these are not returned from any external source, I want to hardcode them into my SELECT) : {'Steve', 'Bill'}. What I now want returned is :

ID   Name   Value   Manager
1    Mike   404     Steve
2    John   404     Steve
1    Mike   404     Bill
2    John   404     Bill

Is it possible to do this? If so, how? :)

Thanks a lot.

score 1 · Accepted Answer · edited May 23 '17 at 12:05

One way is using UNION ALL and CROSS APPLY to join them with your rows:

SELECT p.ID, p.Name ,p.Value, x.Col AS Manager
FROM dbo.Persons p
CROSS APPLY (SELECT Col FROM (SELECT 'Steve' UNION ALL SELECT 'Bill')AS T(Col))X

If the managers are a comma seperated list you need a split function in SQL-Server 2005, for example:

CREATE FUNCTION [dbo].[Split]
(
    @ItemList NVARCHAR(MAX), 
    @delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))  
AS      

BEGIN    
    DECLARE @tempItemList NVARCHAR(MAX)
    SET @tempItemList = @ItemList

    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    SET @tempItemList = REPLACE (@tempItemList, ' ', '')
    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)
    BEGIN
        IF @i = 0
            SET @Item = @tempItemList
        ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
        INSERT INTO @IDTable(Item) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END 
    RETURN
END

Now this works:

SELECT p.ID, p.Name ,p.Value, s.Item AS Manager
FROM dbo.Persons p
CROSS APPLY dbo.Split('Steve,Bill', ',')s

Result (SQL-Fiddle is down):

ID  Name    Value   Manager
1   Mike    404     Steve
2   John    404     Steve
1   Mike    404     Bill
2   John    404     Bill

Great answer and post. Thanks very much. I get the message 'Procedure or function dbo.Split has too many arguments specified.' when I try to run this split function with 3+ arguments - but from a look at the code I can't see why this would be the case; it seems like it should work with >2 arguments. Any idea you know what may be causing this ? — Simon Kiely, Jun 19 '13 at 08:51
Never mind; I am a silly boy! Mismatched strings/commas. Thank you :) — Simon Kiely, Jun 19 '13 at 08:53
@SimonKiely: The `Split` approach can be used if you have a list of comma separated values as `varchar`. So for example (note that it's a single varchar): `dbo.Split('Steve,Bill', ',')`. The first approach using `UNION ALL` to concatenate each hardcoded manager is used if the values are seperated from each other. — Tim Schmelter, Jun 19 '13 at 08:55

score 0 · Answer 2 · answered Jun 19 '13 at 08:20

0

This query will give you the exact result as you want

select ID,Name,Value, Manager  from Test
cross join

(select 'Bill' Manager
union
select 'Steve' Manager) t1

answered Jun 19 '13 at 08:20

Shaikh Farooque

2,620
1
19
33

score 0 · Answer 3 · answered Jun 19 '13 at 08:21

0

Well as you state the question

SELECT A.ID, A.Name, A.Value, B.Manager FROM
(
SELECT ID, Name, Value FROM MyTable
) A
CROSS JOIN
(
SELECT
 'Steve' Manager
UNION
SELECT
 'Bill' Manager
) B

Frankly, Id allways store manager in (its own) table

answered Jun 19 '13 at 08:21

Ian P

1,724
1
10
12

Creating a superset in SQL Server

3 Answers3