June 30, 2017 at 4:49 pm
I had found SQL code previously that would let me use a comma delimited value in one table to return a single row from a lookup table. Unfortunately my computer crashed and I lost everything. Here is an example of the data
Value- 3243,163 (varchar(255))
Lookup table
Id (Int) Name
3243 PPD
163 TCPD
I remember the code used STUFF and FOR XML. Any assistance would be appreciated.
July 1, 2017 at 6:49 am
Kevin Ray - Friday, June 30, 2017 4:49 PMI had found SQL code previously that would let me use a comma delimited value in one table to return a single row from a lookup table. Unfortunately my computer crashed and I lost everything. Here is an example of the dataValue- 3243,163 (varchar(255))
Lookup table
Id (Int) Name
3243 PPD
163 TCPDI remember the code used STUFF and FOR XML. Any assistance would be appreciated.
It sounds like you're describing two different things and I'm not sure which one you want. So, I'll explain both and see if either one is what you're really looking for. They're both good techniques with articles written by a couple of very smart people.
First, we'll need some DDL and data to work with, so we'll create it. The util.dbo.TallyN is a zero-read tally function. For more information on tally tables, see Jeff Moden's article at http://qa.sqlservercentral.com/articles/T-SQL/62867/. It's quite a handy tool to have around.
IF OBJECT_ID('tempdb.dbo.SomeParts', 'u') IS NOT NULL DROP TABLE #SomeParts;
CREATE TABLE #SomeParts (
ID Integer,
Name Varchar(255));
INSERT INTO #SomeParts(ID, Name)
SELECT t.N, 'Part ' + CONVERT(Varchar(9), t.N)
FROM util.dbo.TallyN(5000) t;
If you want to use a comma-delimited list to find rows in a table, here's how. The util.dbo.DelimitedSplit8K function is Jeff Moden's splitter described in his article at http://qa.sqlservercentral.com/articles/Tally+Table/72993/. There are several different query forms you can use with the function; this is one of them that people find intuitive and is easy to understand.
DECLARE @strIDs Varchar(255) = '3243,163';
WITH ctePartsToFind AS (
SELECT ID = CONVERT(Integer, s.Item)
FROM util.dbo.DelimitedSplit8K(@strIDs, ',') s
)
SELECT p.*
FROM #SomeParts p
WHERE p.ID IN (SELECT ID
FROM ctePartsToFind)
ORDER BY p.ID;
If, on the other hand, you're trying to assemble a string of IDs from a table, then Wayne Sheffield has an article on the technique at http://qa.sqlservercentral.com/articles/comma+separated+list/71700/. Here's an example of querying and concatenating the first 10 rows from the table.
SELECT CommaList = STUFF((SELECT TOP 10 ',' + CONVERT(Varchar(9), ID)
FROM #SomeParts
ORDER BY ID
FOR XML PATH(''), TYPE).value('.','varchar(max)'), 1, 1, '')
I hope one of these is the one you're after. If you have questions beyond this, just let me know which one.
July 3, 2017 at 11:17 am
Let me restate my original issue. There is a comma deleted value (sometimes there is only one value) that are indexes (i.e, IDs) into another table that has a value. Thus using the values (3243,163) above I would need the value 'PPD, TCPD' returned in an SQL statement in one row. Unfortunately I cannot use a CTE (in goes into another application that does not support the use of a CTE).
July 3, 2017 at 12:32 pm
Kevin Ray - Monday, July 3, 2017 11:17 AMLet me restate my original issue. There is a comma deleted value (sometimes there is only one value) that are indexes (i.e, IDs) into another table that has a value. Thus using the values (3243,163) above I would need the value 'PPD, TCPD' returned in an SQL statement in one row. Unfortunately I cannot use a CTE (in goes into another application that does not support the use of a CTE).
Here's a fairly simple example:CREATE TABLE dbo.Table1 (
TID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Table1_TID PRIMARY KEY CLUSTERED,
Names varchar(255)
);
INSERT INTO dbo.Table1 (Names)
SELECT '3243,163';
CREATE TABLE dbo.LookupTable (
ID int NOT NULL CONSTRAINT PK_LookupTable_ID PRIMARY KEY CLUSTERED,
[Name] varchar(5)
)
INSERT INTO dbo.LookupTable (ID, [Name])
SELECT 3243, 'PPD' UNION ALL
SELECT 163, 'TCPD';
SELECT T.TID, T.Names,
STUFF((
SELECT ', ' + LT.[Name]
FROM dbo.LookupTable AS LT
OUTER APPLY dbo.DelimitedSplit8K(T.Names, ',') AS S
WHERE LT.ID = CONVERT(int, S.Item)
ORDER BY S.ItemNumber
FOR XML PATH('')
), 1, 2, '') AS NameList
FROM dbo.Table1 AS T
GROUP BY T.TID, T.Names;
DROP TABLE dbo.LookupTable;
DROP TABLE dbo.Table1;
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 3, 2017 at 1:06 pm
I will have to see if I can post the DelimitedSplit8K function to our DB.
Thanks
July 3, 2017 at 1:13 pm
Kevin Ray - Monday, July 3, 2017 1:06 PMSo where does the DelimitedSplit8K come from? Is that a stored procedure?Thanks
Oops! Forgot to reference that. It's an inline-table-valued function, and is available here:
http://qa.sqlservercentral.com/articles/72993/
It performs like a speed demon, which is why it's so popular.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 3, 2017 at 1:50 pm
Thanks, I am still looking for the solution that did not require a function. I believe it used a cross apply. It turns out there a backup of my C: drive (I forgot they took an image recently). But it not available at the moment. Once I can find the code I will post it.
July 3, 2017 at 2:20 pm
sgmunson - Monday, July 3, 2017 1:13 PMKevin Ray - Monday, July 3, 2017 1:06 PMSo where does the DelimitedSplit8K come from? Is that a stored procedure?Thanks
Oops! Forgot to reference that. It's an inline-table-valued function, and is available here:
http://qa.sqlservercentral.com/articles/72993/
It performs like a speed demon, which is why it's so popular.
That's because it was in my original post 2 days ago.
Kevin, it's a really good article and explains how it works and compares the different types of splitters. You can probably show it to your DBAs and they'll allow it.
July 3, 2017 at 3:48 pm
Other staff in our office has rights to created functions, stored procedures, etc. I just don't. I may be able to find the original query or re-create it, which did not need a function. I once had better memory, and I could have remembered what I had 🙂
July 3, 2017 at 4:10 pm
Kevin Ray - Monday, July 3, 2017 3:48 PMOther staff in our office has rights to created functions, stored procedures, etc. I just don't. I may be able to find the original query or re-create it, which did not need a function. I once had better memory, and I could have remembered what I had 🙂
Do you have a database of utility functions that anyone can use? If not, try to get them to consider creating one. Then, put the DelimitedSplit8K function in it and grant SELECT privs to public. You can also grant VIEW DEFINITION to public if you like to encourage others to learn from it. Naturally, you'll need an IT owner or champion of it. Since it doesn't contain any actual data, there's nothing sensitive in there to worry about. Naturally, you'll need an IT owner or champion of it.
I find this centralized library will grow over time. As more people use it, the more stuff they'll have to add to it and the more they'll use it. When functions are available to perform common tasks efficiently, people don't have to reinvent the wheel every time. I created this where I work and it's proven to be very useful.
July 5, 2017 at 1:43 pm
Got the back up info. So a little explanation. EventAttribute contains a value (can be a comma delimited value) that is a FK into the Lookup table. The AttributeDef table has the 'kind' of attribute I am looking for. The eventAttribute.ToTime filter just gets the most current value(s) and eventAttribute.eventID=e.ID is a link to the main table (I use this code as a sub-query). But do not ask me how it really works.
SELECT stuff(
(SELECT
','+description
FROM
dbo.Lookup
WHERE id IN (SELECT t.c.value('.', 'VARCHAR(20)')
FROM (SELECT x = CAST('<t>' + REPLACE(ea.[value], ',', '</t><t>') + '</t>' AS XML)) a
CROSS APPLY x.nodes('/t') t(c)) for XML Path('')),1,1,'')
from
dbo.EventAttribute as ea
INNER JOIN dbo.AttributeDef as ad on ad.Id=ea.AttributeId
where ad.[Description] = 'Contact Reason'
and ea.ToTime is null
and ea.EventId = e.id
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply