Concatenating within SQL Command

[Former Member]

Hello,

I need to do the following concatenation in SQL and add it into Command. I know how to do it in Crystal using subreports and shared variables using runtime concatenations, when I export it into Excel, the data returned in excel is not aligned properly.

I'd like to ask you guys for assistance on how I can do the following in SQL:

The data looks as follows:

check#      check_line     desc_line        des
12233          100                 1        Joe Smith Trip to Vegas
12233          200                 1        Cab pickup @ 5:45pm to Palms  wait
12233          200                 2        time 12 minutes.
12233          300                 1        Meals
12233          400                 1        Dinner w/ Jane Doe at Planet
12233          400                 2        Hollywood.

How can I get the result to look like this in SQL?:

check#      check_line            des
12233          100                Joe Smith Trip to Vegas
12233          200                Cab pickup @ 5:45pm to Palms  wait time 12 minutes.
12233          300                Meals
12233          400                Dinner w/ Jane Doe at Planet Hollywood.

Many thanks in advance,

Regards,

Zack H.

[Former Member · Accepted solution]

Easy... Try something like this:

SELECT
c1.check#,
c1.check_line,
c1.dec + ' ' + c2.des + ' ' + c3.des AS des
FROM (
	SELECT
	check#,
	check_line,
	dec
	FROM tblChecks
	WHERE desc_line = 1) AS c1
LEFT OUTER JOIN (
	SELECT
	check#,
	check_line,
	dec
	FROM tblChecks
	WHERE desc_line = 2) AS c2 ON c1.check# = c2.check# AND c1.check_line = c2.check_line
LEFT OUTER JOIN (
	SELECT
	check#,
	check_line,
	dec
	FROM tblChecks
	WHERE desc_line = 3) AS c3 ON c2.check# = c3.check# AND c2.check_line = c3.check_line

If there is a possibility of a 4 or more in the desc_line, just follow the pattern to allow for more lines.

HTH,

Jason

[Former Member · Accepted solution]

Jason,

Thanks for your prompt response.

Two things:

1) I am getting null values for des

2) Is there a query that will auto-determine the # of records it needs to parse through to get me the concatenation. The number of records with multiple description lines are limitless.

Any ideas?

Thanks again!

Regards,

Zack H.

[Former Member · Accepted solution]

Oops my bad. Sorry. Try it like this instead:

SELECT
c1.check#,
c1.check_line,
c1.dec + 
	CASE WHEN c2.des IS NULL THEN '' ELSE ' ' + c2.des END +
	CASE WHEN c3.des IS NULL THEN '' ELSE ' ' + c3.des END AS des
FROM (
	SELECT
	check#,
	check_line,
	dec
	FROM tblChecks
	WHERE desc_line = 1) AS c1
LEFT OUTER JOIN (
	SELECT
	check#,
	check_line,
	dec
	FROM tblChecks
	WHERE desc_line = 2) AS c2 ON c1.check# = c2.check# AND c1.check_line = c2.check_line
LEFT OUTER JOIN (
	SELECT
	check#,
	check_line,
	dec
	FROM tblChecks
	WHERE desc_line = 3) AS c3 ON c2.check# = c3.check# AND c2.check_line = c3.check_line

The CASE statements check for nulls and converts them to empty strings to prevent the entire field from showing up as null.

Jason

[Former Member · Accepted solution]

Jason,

I have to give you credit. This will work. Thanks!

However, I'd still like to know if there is a way for SQL to know when to stop on its own rather than having to built multiple different LEFT OUTER JOINS?

Any suggestions?

Regards,

Zack H.

[Former Member · Accepted solution]

Actually there is a way to do it using a "WHILE LOOP" and using it to populate a temp table. Hold on and I'll write it up...

You caught me on a nothin to do day.

[Former Member · Accepted solution]

Awesome...thanks Jason!

[Former Member · Accepted solution]

Well here goes nothing... It's tough to debug when there is no DB to run against... and it's starting to make me a little cross eyed.

CREATE TABLE #Checks (
ID INT, RN INT, CheckNum INT, Check_Line INT, Desc_Line INT, Des VarChar(MAX), Checked INT)
INSERT INTO #Checks (ID, RN, CheckNum, Check_Line, Desc_Line, Des, Checked)
SELECT 
ROW_NUMBER() OVER(ORDER BY check#, check_line, desc_line), 
ROW_NUMBER() OVER(PARTITION BY check_line ORDER BY check#, check_line, desc_line), 
check#,
check_line,
desc_line,
des,
0 AS Checked
FROM tblChecks

DECLARE @CheckNum INT, @Check_Line INT, @Desc_Line INT, @ID INT, @Des VarChar(MAX)
SELECT @CheckNum = Min(CheckNum) FROM #Checks 
	WHERE Checked = 0
SELECT @Check_Line = Min(Check_Line) FROM #Checks 
	WHERE CheckNum = @CheckNum AND Checked = 0
SELECT @Desc_Line = Min(Desc_Line) FROM #Checks 
	WHERE CheckNum = @CheckNum AND Check_Line = @Check_Line AND Checked = 0
SELECT @ID = Min(ID) FROM #Checks
	WHERE @CheckNum AND Check_Line = @Check_Line AND CheckNum = @CheckNum AND Checked = 0
SELECT @Des = Des FROM #Checks
	WHERE ID = @ID

CREATE TABLE #Array (
CheckNum INT, Check_Line INT, Des VarChar(MAX))
INSERT INTO #Array (CheckNum, Check_Line, Des)
SELECT CheckNum, Check_Line, Des FROM #Checks WHERE Desc_Line = 1

WHILE EXISTS (SELECT TOP 1 RN FROM #Checks WHERE Checked = 0)
BEGIN
UPDATE #Checks SET Checked = 1 WHERE ID = @ID
SELECT @Des = Des FROM #Checks WHERE RN = 1 AND CheckNum = @CheckNum AND Check_Line = @Check_Line
SELECT @Des = @Dec + ' ' + Des FROM #Checks WHERE RN > 1 AND CheckNum = @CheckNum AND Check_Line = @Check_Line
UPDATE #Array SET Des = @Des WHERE CheckNum = @CheckNum AND Check_Line = @Check_Line
SELECT @CheckNum = Min(CheckNum) FROM #Checks 
	WHERE Checked = 0
SELECT @Check_Line = Min(Check_Line) FROM #Checks 
	WHERE CheckNum = @CheckNum AND Checked = 0
SELECT @Desc_Line = Min(Desc_Line) FROM #Checks 
	WHERE CheckNum = @CheckNum AND Check_Line = @Check_Line AND Checked = 0
SELECT @ID = Min(ID) FROM #Checks
	WHERE @CheckNum AND Check_Line = @Check_Line AND CheckNum = @CheckNum AND Checked = 0
END

SELECT * FROM #Array

DROP TABLE #Checks
DROP TABLE #Array

Note this will only run in SQL Server 2005 and later (earlier versions don't support the ROW_NUMBER() function.

You didn't give me a primary key so I created one called ID...

Give it a try and see how it works.

Jason

[Former Member · Accepted solution]

Wow, Jason. This looks exhausting. Thanks for putting this together for me. At least I gave you something to do today =).

I haven't had a chance to look at it yet but I will first thing tomorrow morning.

Thanks again and I truly appreciate your help.

Regards,

Zack H.

[Former Member · Accepted solution]

No problem. As you can see the 1st was is defiantly MUCH simpler.

If it doesn't work...Sorry... I'm on vacation until after the 1st of the year. 😄

[Former Member · Accepted solution]

Jason,

Please don't apologize. I have already used your first method into my reports and they are already published in Infoview for my users. I'm always looking to make the queries as self-sufficient as possible.

Enjoy your vacation and enjoy the holidays. You did great today! Thank you!

Regards,

Zack H.