T-SQL query, multiple values in a field

Question

I have two tables in a database. The first table tblTracker contains many columns, but the column of particular interest is called siteAdmin and each row in that column can contain multiple loginIDs of 5 digits like 21457, 21456 or just one like 21444. The next table users contains columns like LoginID, fname, and lname.

What I would like to be able to do is take the loginIDs contained in tblTracker.siteAdmin and return fname + lname from users. I can successfully do this when there is only one loginID in the row such as 21444 but I cannot figure out how to do this when there is more than one like 21457, 21456.

Here is the SQL statement I use for when there is one loginID in that column

SELECT b.FName + '' '' + b.LName AS siteAdminName,
FROM tblTracker a
LEFT OUTER JOIN users b ON a.siteAdmin= b.Login_Id

However this doesn't work when it tries to join a siteAdmin with more than one LoginID in it

Thanks!

Is it multiple values in one column or just one column, please do not tell me you have multiple values seperated by a comma ? — JonH
– JonH, Commented Jul 2, 2012 at 19:07
the siteAdmin is a single column with multiple values separated by commas. it isnt ideal, but its what I inherited. I cant rock the boat by changing how things are stored in the database cause that will break god knows what. — Mike
– Mike, Commented Jul 2, 2012 at 19:18
thank you for your comment. I didnt know the Bells and Whistles were important to people, I will make sure to accept answers in the future. — Mike
– Mike, Commented Jul 2, 2012 at 19:39
@Mike, I find it very disappointing when I spend my time answering a question and the OP (person asking the question) doesn't select an answer. I understand that it is possible that none of the answers were what the OP was looking for. In that case the OP needs to add comments and/or edit the question the clarify and/or add additional info. It is up to the OP help the people trying to answer the question. by leaving a lot of questions unaccepted, it shows that the OP just throws out a bunch of questions with little regard to getting them resolved. in that case I avoid answering. — KM.
– KM., Commented Jul 2, 2012 at 20:09

KM. · Accepted Answer · 2012-07-03 11:50:04Z

I prefer the number table approach to split a string in TSQL

For this method to work, you need to do this one time table setup:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this split function:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO

You can now easily split a CSV string into a table and join on it:

select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')

OUTPUT:

ListValue
-----------------------
1
2
3
4
5
6777

(6 row(s) affected)

Your can now use a CROSS APPLY to split every row in your table like:

DECLARE @users table (LoginID int, fname varchar(5), lname varchar(5))
INSERT INTO @users VALUES (1, 'Sam', 'Jones')
INSERT INTO @users VALUES (2, 'Don', 'Smith')
INSERT INTO @users VALUES (3, 'Joe', 'Doe')
INSERT INTO @users VALUES (4, 'Tim', 'White')
INSERT INTO @users VALUES (5, 'Matt', 'Davis')
INSERT INTO @users VALUES (15,'Sue', 'Me')

DECLARE @tblTracker  table (RowID int, siteAdmin varchar(50))
INSERT INTO @tblTracker VALUES (1,'1,2,3')
INSERT INTO @tblTracker VALUES (2,'2,3,4')
INSERT INTO @tblTracker VALUES (3,'1,5')
INSERT INTO @tblTracker VALUES (4,'1')
INSERT INTO @tblTracker VALUES (5,'5')
INSERT INTO @tblTracker VALUES (6,'')
INSERT INTO @tblTracker VALUES (7,'8,9,10')
INSERT INTO @tblTracker VALUES (8,'1,15,3,4,5')

SELECT
    t.RowID, u.LoginID, u.fname+' '+u.lname AS YourAdmin
    FROM @tblTracker                                     t
        CROSS APPLY dbo.FN_ListToTable(',',t.siteAdmin)  st
        LEFT OUTER JOIN @users                           u ON st.ListValue=u.LoginID --to get all rows even if missing siteAdmin
        --INNER JOIN @users                                u ON st.ListValue=u.LoginID  --to remove rows without any siteAdmin
    ORDER BY t.RowID,u.fname,u.lname

OUTPUT:

RowID       LoginID     YourAdmin
----------- ----------- -----------
1           2           Don Smith
1           3           Joe Doe
1           1           Sam Jones
2           2           Don Smith
2           3           Joe Doe
2           4           Tim White
3           5           Matt Davis
3           1           Sam Jones
4           1           Sam Jones
5           5           Matt Davis
7           NULL        NULL
7           NULL        NULL
7           NULL        NULL
8           3           Joe Doe
8           5           Matt Davis
8           1           Sam Jones
8           15          Sue Me
8           4           Tim White

(18 row(s) affected)

Collectives™ on Stack Overflow

T-SQL query, multiple values in a field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related