Return multiple identical records

Hello,
I'm not feeling too creative today.  I need to query some data for printing tags. Looking at the 'bp' field, if it is an S = single, D = Double, T = Triple, Q = Quadruple. So if the bp = Q then I need to return 4 of that same record. The below accomplished this, but what would be a more compact way of doing it?

Declare @Brand char(2)
Declare @Prog varchar(20)

set @Brand = 'AG'
set @Prog = 'FD'

Declare @Results Table (program varchar(20),
						wAgin varchar(10),
						mrkNbr varchar(20),
						brand  char(2),
						ofln	char(4),
						bp	char(1)
						)
						
--Single Level
INSERT INTO @Results
SELECT program,wAgin,mrkNbr,SUBSTRING(loc,3,2) Brand, SUBSTRING(fill,5,4) Ofln,bp
FROM tbl_lsu
WHERE SUBSTRING(loc,3,2) = @Brand
AND bp = 'S'
AND program = @Prog

--Double Level
INSERT INTO @Results
SELECT program,wAgin,mrkNbr,SUBSTRING(loc,3,2) Brand, SUBSTRING(fill,5,4) Ofln,bp
FROM tbl_lsu
WHERE SUBSTRING(loc,3,2) = @Brand
AND bp = 'D'
AND program = @Prog
INSERT INTO @Results
SELECT program,wAgin,mrkNbr,SUBSTRING(loc,3,2) Brand, SUBSTRING(fill,5,4) Ofln,bp
FROM tbl_lsu
WHERE SUBSTRING(loc,3,2) = @Brand
AND bp = 'D'
AND program = @Prog

--Triple Level
INSERT INTO @Results
SELECT program,wAgin,mrkNbr,SUBSTRING(loc,3,2) Brand, SUBSTRING(fill,5,4) Ofln,bp
FROM tbl_lsu
WHERE SUBSTRING(loc,3,2) = @Brand
AND bp = 'T'
AND program = @Prog
INSERT INTO @Results
SELECT program,wAgin,mrkNbr,SUBSTRING(loc,3,2) Brand, SUBSTRING(fill,5,4) Ofln,bp
FROM tbl_lsu
WHERE SUBSTRING(loc,3,2) = @Brand
AND bp = 'T'
AND program = @Prog
INSERT INTO @Results
SELECT program,wAgin,mrkNbr,SUBSTRING(loc,3,2) Brand, SUBSTRING(fill,5,4) Ofln,bp
FROM tbl_lsu
WHERE SUBSTRING(loc,3,2) = @Brand
AND bp = 'T'
AND program = @Prog

--Quad Level
INSERT INTO @Results
SELECT program,wAgin,mrkNbr,SUBSTRING(loc,3,2) Brand, SUBSTRING(fill,5,4) Ofln,bp
FROM tbl_lsu
WHERE SUBSTRING(loc,3,2) = @Brand
AND bp = 'Q'
AND program = @Prog
INSERT INTO @Results
SELECT program,wAgin,mrkNbr,SUBSTRING(loc,3,2) Brand, SUBSTRING(fill,5,4) Ofln,bp
FROM tbl_lsu
WHERE SUBSTRING(loc,3,2) = @Brand
AND bp = 'Q'
AND program = @Prog
INSERT INTO @Results
SELECT program,wAgin,mrkNbr,SUBSTRING(loc,3,2) Brand, SUBSTRING(fill,5,4) Ofln,bp
FROM tbl_lsu
WHERE SUBSTRING(loc,3,2) = @Brand
AND bp = 'Q'
AND program = @Prog
INSERT INTO @Results
SELECT program,wAgin,mrkNbr,SUBSTRING(loc,3,2) Brand, SUBSTRING(fill,5,4) Ofln,bp
FROM tbl_lsu
WHERE SUBSTRING(loc,3,2) = @Brand
AND bp = 'Q'
AND program = @Prog

select * from @Results Order By ofln

Open in new window

coperations07Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
When I need to do something like this, I include a second table in my query .  This table (tbl_Numbers) contains a single field (intNumber) and 10 values (the #'s 0 - 9).

With this table, you could write a single select statement that would look something like:

SELECT N.intNumber, program,wAgin,mrkNbr,SUBSTRING(loc,3,2) Brand, SUBSTRING(fill,5,4) Ofln,bp
FROM tbl_lsu, tbl_Numbers as N
WHERE SUBSTRING(loc,3,2) = @Brand
AND n.intNumber > 0
AND n.intNumber <= CASE WHEN bp = 'S' Then 1
                                                 WHEN bp = 'D' Then 2
                                                 WHEN bp = 'T' Then 3
                                                 WHEN bp = 'Q' Then 4
                                                 ELSE 1 END
0
dsackerContract ERP Admin/ConsultantCommented:
You might like using a little CTE recursive magic:
;WITH myCTE AS
(
    SELECT n = 1
    UNION ALL
    SELECT n + 1
    FROM   myCTE
    WHERE  n < 99   -- Just in case you ever want to go higher
)
INSERT INTO @Results
SELECT program,wAgin,mrkNbr,SUBSTRING(loc,3,2) Brand, SUBSTRING(fill,5,4) Ofln,bp
FROM tbl_lsu, myCTE
WHERE SUBSTRING(loc,3,2) = @Brand
AND program = @Prog
AND n <= CASE bp
            WHEN 'S' THEN 1
            WHEN 'D' THEN 2
            WHEN 'T' THEN 3
            WHEN 'Q' THEN 4
         END

Open in new window

This recursive CTE simply creates a results table of 99 rows, where n = 1 thru n = 99.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
INSERT INTO @Results
SELECT program,wAgin,mrkNbr,SUBSTRING(loc,3,2) Brand, SUBSTRING(fill,5,4) Ofln,bp
FROM tbl_lsu
CROSS APPLY (
    SELECT 1 AS bp_repeater UNION ALL
    SELECT 2 WHERE bp IN ( 'D', 'T', 'Q' ) UNION ALL
    SELECT 3 WHERE bp IN ( 'T', 'Q' ) UNION ALL
    SELECT 4 WHERE bp IN ( 'Q' )
) AS bp_repeater
WHERE SUBSTRING(loc,3,2) = @Brand
AND program = @Prog
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

coperations07Author Commented:
Great stuff guys! I'm going to go with the CTE solution, but the Cross Apply works great also.
0
Scott PletcherSenior DBACommented:
I'd still strongly recommend using an ANSI-style join.  The implied JOIN in the FROM clause is a very obsolete style of coding, and if accidentally forget to specify join criteria, or it gets deleted, you end up with an unexpected CROSS JOIN:

--WITH ...
INSERT INTO @Results
SELECT program,wAgin,mrkNbr,SUBSTRING(loc,3,2) Brand, SUBSTRING(fill,5,4) Ofln,bp
FROM tbl_lsu
INNER JOIN myCTE ON
    n <= CASE bp
            WHEN 'S' THEN 1
            WHEN 'D' THEN 2
            WHEN 'T' THEN 3
            WHEN 'Q' THEN 4
         END
WHERE SUBSTRING(loc,3,2) = @Brand
AND program = @Prog
0
dsackerContract ERP Admin/ConsultantCommented:
Hi coperations07,

Glad you liked the CTE. You'll do quite fine with either a WHERE join or an INNER join with the myCTE table. Bottom line: it's versatility gives you room for growth without having to add a bunch of extra CROSS APPLY ... SELECT ... UNION ALL lines, which was a weak alternate option.
0
Scott PletcherSenior DBACommented:
Won't you have to change the CASE statement?  So what's the difference?

Implied JOINs have been deprecated and will be removed from SQL Server.  You're just forcing yourself to do clean-up later.
0
Scott PletcherSenior DBACommented:
Recursion is also very slow.  While there's nothing wrong with a full list of numbers per se, use CROSS JOIN to generate those numbers if you want to spend the time to gen 99 numbers to handle your requirement for 4 numbers.
0
Dale FyeCommented:
Scott,

"Implied JOINs have been deprecated"

1.  What is an "implied join"?  You are not referring to a Cartesian join are you?
2.  Where is the implied join here?

My problem is that I still don't understand CTEs, so I continue to use my numbers table and views to generate sets of continuous numbers.
0
Scott PletcherSenior DBACommented:
An "implied JOIN" is tables separated by only commas in the FROM clause, like this:

FROM tableA, tableB

ANSI-JOIN is when the type of JOIN is explicitly specified in the FROM clause, as has been standard coding practice for at least a decade:

FROM tableA
INNER JOIN tableB ON ...

What's really deprecated is implied OUTER joins, because they can be genuinely ambiguous when coded in the WHERE clause.

Most people consider the explicit JOINs clearer and easier to follow.  And so many people have accidentally gotten Cartesian joins using implied joins, that it's also safer to use explicit JOINs in the FROM clause.
0
Dale FyeCommented:
Thanks, for the explanation, Scott.

So, SQL Server has not actually deprecated the use of:

FROM TableA, TableB

I have not had an opportunity to move beyond 2008 R2, so I'm just checking to make sure that this structure has not been deprecated in later versions

I understand the down sides of doing such, and am going to read up on the CROSS APPLY syntax you refer to above.  I've only recently returned to using SQL Server on a regular basis, after over 10 years away, so I have a lot of 'new' SQL Server syntax to learn and understand.
0
Scott PletcherSenior DBACommented:
Technically I'm not sure that implied INNER joins are deprecated, although the link below does state that they are.

As for me, I'd much rather be consistent in coding.  Since I must code OUTER joins with a JOIN clause, why not code INNER joins the same way?

https://msdn.microsoft.com/en-us/library/dd172122.aspx
"
-- Example 1: Deprecated syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] = [T2].[ID]

-- Example 2: Current syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] AS T2
INNER JOIN [dbo].[Table1] as T1
ON [T2].[ID] = [T2].[ID]
"
0
Dale FyeCommented:
I'm all for using the INNER JOIN syntax, the only time I ever use the Example 1 syntax is when I want multiple rows for each record in TableA.

As an Example, I might do something like:

SELECT A.ID
, A.StartDate, A.EndDate, DateAdd(day, B.intNumber, A.StartDate) as OccDate
FROM TableA as A, TableB as B
WHERE DateAdd(day, B.intNumber, A.StartDate) < = A.EndDate

to get a list of all of the days between A.StartDate and A.EndDate.  I'm sure there is a way to accomplish that with your CROSS APPLY as well, and will experiment with that.  I guess that would look something like:

SELECT A.ID
, A.StartDate, A.EndDate
FROM TableA as A
CROSS APPLY (
SELECT DateAdd(day, B.intNumber, A.StartDate) < = A.EndDate
FROM TableB as B
) as OccDate
0
Scott PletcherSenior DBACommented:
That could also be done as in INNER JOIN:

FROM TableA as A
INNER JOIN TableB as B ON DateAdd(day, B.intNumber, A.StartDate) < = A.EndDate
0
Dale FyeCommented:
Thanks, Scott.  Appreciate the ideas.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.