November 14, 2023 at 8:16 pm
I currently have a table such as:
id code
1 1
2 10,2
3 40
4 29,9
I have another table that contains those code such as:
id code descscription
1 1 NW
2 10 NE
3 2 SE
4 29 SW
5 9 S
is there a way to query the top table with the 2 values in the column? I know I can do something like
select code from table1 where code like '%10%' and code like '%2%'
however, only one value could be passed in as well such as:
select * from table1 where code = 1
what would be the best way to accomplish this? Would I have to put this in stored procedure and use different parameters and if X and Y are NULL then do query 2, or is there a way to combine this into one query or should I just separate the column into two and place each value in one column?
November 14, 2023 at 10:29 pm
My opinion, I'd redesign the original table to not allow varchar in the column and force ints... but if you are stuck with that, I would look at a string split function. I believe 2019 had that built in, so just call that function to split the column values out to rows and then do an exist on it.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 14, 2023 at 10:55 pm
Oh, owww... Normalization is your friend. You should read Jeff Moden's article on his function DelimitedSplit8K
basically, you have to create the function in a database somewhere. (I'm in TempDB but calling the function which lives in another database "Scratch".
Here's code to query your data into a normalized shape:
use tempdb;
go
/*
-- SETUP
CREATE TABLE datos(id int not null, code VARCHAR(10) not null);
GO
INSERT INTO datos
VALUES
(1, '1'),
(2, '10,2'),
(3, '40'),
(4, '29,9');
*/
--Query
/* this is the outermost totals query */SELECT x.id,
x.code,
TotalQty = SUM(x.qty)
FROM
(
/* this section returns the data from the cross apply, and casts the Quantity to Integer) */SELECT d.ID
, d.code
, ca.ItemNumber
, Qty = CAST(ca.Item AS INT)
FROM datos d
/* this section does the splitting of the text string into chunks */CROSS APPLY (
SELECT *
FROM Scratch.dbo.delimitedsplit8k(d.code,',')
WHERE id = d.id
) ca
)x
GROUP BY x.id,
x.code;
Note: the result needs to be cast back to numbers (the column is defined as text because you can't store more than one value in a numeric column.) The best option would be to normalize first, and then write queries, but we're not always fortunate enough to have the database rights we need to do that.
Do this one "layer" at a time. (basically from the bottom up... the comment explains what each section is doing)
Oh, I'm an idiot. you're doing a simple filter... here:
SELECT x.id,
x.code,
x.Qty
FROM
(
SELECT d.ID
, d.code
, ca.ItemNumber
, Qty = CAST(ca.Item AS INT)
FROM datos d
CROSS APPLY (
SELECT *
FROM Scratch.dbo.delimitedsplit8k(d.code,',')
WHERE id = d.id
) ca
)x
WHERE x.qty = 1;
hope this helps! (this is the split stuff that Brian was talking about... well sort of)
Pieter
November 15, 2023 at 12:26 pm
I can modify the table very little, not much due to it's used by another app. Thanks, I'll take a look and see if I can get the recommended solution working in my scenario thanks
November 15, 2023 at 3:56 pm
Okay, here's my solution assuming you can't modify the table. Sometimes you just have to live with a bad design. If the table is really big, it might be worth inserting the result of the splitter stuff into a separate table and indexing it for performance. Totally missed the join to the existing table!
use tempdb;
go
-- SETUP: Codes
CREATE TABLE codigos (
id int not null,
code int not null,
descrip char(2) NOT NULL);
GO
INSERT INTO codigos VALUES
(1, 1, 'NW'),
(2, 10, 'NE'),
(3, 2, 'SE'),
(4, 29, 'SW'),
(5, 9, 'S');
-- facts
CREATE TABLE datos(
id int not null,
code VARCHAR(10) not null
);
GO
INSERT INTO datos
VALUES
(1, '1'),
(2, '10,2'),
(3, '40'),
(4, '29,9');
/* solution: the SUM(itm) is maybe not necessary */
SELECT descrip,
ItemCount = sum(itm)
FROM
(
SELECT c.descrip,
items.itm
FROM codigos c
INNER JOIN
/* Splitter Query - returns (id, itm)*/
(SELECT id,
--code,
itm = CAST(ca.Item AS INT)
FROM datos d
CROSS APPLY Scratch.dbo.DelimitedSplit8K(d.code,',') ca) items
ON c.id = items.id
) itmData
GROUP BY itmData.descrip;
Sorry about the weird names... just didn't feel like dealing with reserved words in TSQL, which are always in English. =(
If performance is terrible, you'd take the result of "Splitter Query" section and write that somewhere. Maybe a table in tempdb or a permanent table and indexed.
November 15, 2023 at 4:58 pm
So, after a 2 hour meeting, I can make whatever changes to the table as needed. So, to make this work, I could create a new row for each code or no?
So something like this:
Table 1
id code
1 1
2 10
3 2
4 40
5 29
6 9
and my lookup table would remain the same: They're joined by the Code:
id code descscription
1 1 NW
2 10 NE
3 2 SE
4 29 SW
5 9 S
so everything on the row would be the same except for the { Codes }, reach row would have it's own code
November 15, 2023 at 5:23 pm
Do the Id columns in these tables perform any useful function? Based on what you've posted so far, it looks like there's an argument for removing them ... or maybe just in the lookup table.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 15, 2023 at 5:34 pm
The { Code } column is used to see what region the logged in user can see. The ID column is just an ID column for that row and is the unique identifier for that row, but the { Code } column is used
November 15, 2023 at 5:37 pm
OK, my point was that if the Code is already a candidate key in a table, why not throw away the Id and make Code the clustered PK? Could give you a performance boost too.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 15, 2023 at 8:38 pm
That table has more then the { id } and { code } in it. I only showed those two columns do to the { code } column can show more then once value in it. The table ahs 20 columns in it and the { id } column is the PK already on the table.
November 15, 2023 at 8:49 pm
That's what the CROSS APPLY basically does. Test out the code I wrote to see if it accomplishes what you need. It basically converts all the values in the comma-delimited list to separate records.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply