Multiple rows in one field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple rows in one field

Author

Topic

jaimealvarez
Starting Member

31 Posts

Posted - 2012-02-03 : 11:09:46

Hi, I've seen a couple of answers to this question but don't seem to work for me. CONCAT_GROUP won't work for me. I'm writing a sql script to use in a crystal report and I need to group multiple lines in one line.

Table: cddts

ID line Description
1 100 TSS
1 200 XTT
2 100 PPT
2 200 TTX
2 300 QPT

So I wil like it to show as:

ID Description
1 TSS XTT
2 PPT TTX QPT

I will appreciate any help. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 11:18:16

[code]
SELECT ID,
STUFF((SELECT ' ' + Description FROM cddts WHERE ID = t.ID ORDER BY line FOR XML PATH('')),1,1,'') AS [Description]
FROM (SELECT DISTINCT ID FROM cddts)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-03 : 11:36:27

Visakh -- I am surprised at you. Not going to use an APPLY???


SELECT
    t.[ID]
    , RTRIM(t2.[Description]) AS [Description]
FROM
    ( SELECT DISTINCT [ID] FROM cddts ) AS t
    OUTER APPLY (
        SELECT t2.[Description] + ' '
        FROM cddts AS t2
        WHERE t2.[ID] = t.[ID]
        ORDER BY t2.[Line]
        FOR XML PATH ('')
        )
        AS d ([Description])

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jaimealvarez
Starting Member

31 Posts

Posted - 2012-02-03 : 14:20:40

You guys are great. Thank you very much

jaimealvarez
Starting Member

31 Posts

Posted - 2012-02-03 : 14:39:56

Thank you for this, appreciate it. Can I add one more lvel to this formula? For example if it was

ID line Txl Description
1 100 1 SSS
1 100 2 TTT
1 100 3 PPP
1 200 1 AAA
1 200 2 BBB
2 100 1 LLL
2 100 2 MMM

ID line Description
1 100 SSS TTT PPP
1 200 AAA BBB
2 100 LLL MMM

Thanks for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 15:58:30

[code]
SELECT ID,
STUFF((SELECT ' ' + Description FROM cddts WHERE ID = t.ID AND line = t.line ORDER BY Txl FOR XML PATH('')),1,1,'') AS [Description]
FROM (SELECT DISTINCT ID,Line FROM cddts)t

or

SELECT
t.[ID],t.line
, RTRIM(t2.[Description]) AS [Description]
FROM
( SELECT DISTINCT [ID],line FROM cddts ) AS t
OUTER APPLY (
SELECT t2.[Description] + ' '
FROM cddts AS t2
WHERE t2.[ID] = t.[ID]
AND t2.line = t.line
ORDER BY t2.[Txl]
FOR XML PATH ('')
)
AS d ([Description])

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jaimealvarez
Starting Member

31 Posts

Posted - 2012-02-06 : 16:48:53

Thank you I end up using this script which works great!

SELECT vo_id, voline,
STUFF((SELECT ' ' + des FROM son_db.dbo.apvodes WHERE vo_id = t.vo_id AND voline = t.voline ORDER BY dsline FOR XML PATH('')),1,1,'') AS [des]
FROM (SELECT DISTINCT vo_id,voline FROM son_db.dbo.apvodes)t

I'm new to this type of code, can you please tell me how will I add another table for this? For example I have another table where the vo_id and voline will match the results of this script:

Table: son_db.dbo.csdt

I want to bring two fields from this table (checknum and amt) where vo_id and voline from son_db.dbo.apvodes = vo_id and voline from son_db.dbo.csdt

Thank you, one more question. I'm new to this forum, do I have to do something once my issue is resolved (like closing the question)?
Thanks again for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 16:54:59

can you post some data from your new table and then explain how it needs to be added to output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jaimealvarez
Starting Member

31 Posts

Posted - 2012-02-07 : 09:04:59

sure,
So currently with

SELECT vo_id, voline,
STUFF((SELECT ' ' + des FROM son_db.dbo.apvodes WHERE vo_id = t.vo_id AND voline = t.voline ORDER BY dsline FOR XML PATH('')),1,1,'') AS [des]
FROM (SELECT DISTINCT vo_id,voline FROM son_db.dbo.apvodes)t

this is what I'm seeing:

vo_id voline des

1 100 AAA

1 200 BBB

2 100 CCC

2 200 DDD

2 300 EEE

I have another table that looks like this

Table: son_db.dbo.csddt

Vo_ID chline Num Amount

1 100 123 200

1 200 456 350

2 100 789 400

2 200 1010 800

2 300 1000 1200

I want it to look like this:

vo_id chline/voline num amount des

1 100 123 200 AAA

Thank you.

jaimealvarez
Starting Member

31 Posts

Posted - 2012-02-07 : 09:33:16

I think I'm close with the script below to my final table, I just seem to have a problem with the las part of it (with the INNER JOINs), please take a look. Thank you

WITH query1 AS (SELECT vo_id, voline,
STUFF((SELECT ' ' + des FROM son_db.dbo.apvodes WHERE vo_id = t.vo_id AND voline = t.voline ORDER BY dsline FOR XML PATH('')),1,1,'') AS [des]
FROM (SELECT DISTINCT vo_id,voline FROM son_db.dbo.apvodes)t)

SELECT "csddt"."ckdate" AS Date, "csddt"."cknum" AS 'Check #/GJ ID', NULL AS 'GL#',"csddt"."ckline" AS Line, "csddt"."apnum" As 'Vendor#', "ap"."apname" As 'Vendor Name', "apaddr"."appynm" As Payee, "csddt"."glnum" As 'GL Acct', "gl"."gldes" As 'GL Description', "csd"."voiddt" As 'Void Date', "csddt"."currency" As Currency, "csddt"."vo_id" As 'Voucher ID',
CASE when "csddt"."amt" > 0
then "csddt"."amt"
else 0 end AS Debit,
CASE when "csddt"."amt" < 0
then "csddt"."amt" * -1
else 0 end AS Credit,
"query1"."des" AS 'description'

FROM ( (("son_db"."dbo"."csddt" "csddt" INNER JOIN "son_db"."dbo"."csd" "csd" ON ("csddt"."cknum"="csd"."cknum") AND ("csddt"."baid"="csd"."baid")) INNER JOIN "son_db"."dbo"."apaddr" "apaddr" ON ("csddt"."apnum"="apaddr"."apnum") AND ("csddt"."apaddid"="apaddr"."apaddid")) INNER JOIN "son_db"."dbo"."gl" "gl" ON ("csddt"."glnum"="gl"."glnum") AND ("csddt"."glrc1"="gl"."glrc1")) LEFT OUTER JOIN "son_db"."dbo"."ap" "ap" ON "csddt"."apnum"="ap"."apnum" INNER JOIN "query1" ("csddt"."vo_id" = "query1"."vo_id" AND "csddt"."ckline" = "query1"."voline")

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-07 : 10:21:38

you don't need the double quotes round the object names.

In sql server they should be sentineled by square braces if you do need them.

A bit of formatting wouldn't hurt either when debugging if for no other reasons..

here's your mass of joins formatted a little.


FROM
	( 
		(
			(
			"son_db"."dbo"."csddt" "csddt"
			INNER JOIN "son_db"."dbo"."csd" "csd" ON
 				("csddt"."cknum"="csd"."cknum")
				AND ("csddt"."baid"="csd"."baid")
			)
			INNER JOIN "son_db"."dbo"."apaddr" "apaddr" ON
 				("csddt"."apnum"="apaddr"."apnum")
				AND ("csddt"."apaddid"="apaddr"."apaddid")
		)
		INNER JOIN "son_db"."dbo"."gl" "gl" ON
			("csddt"."glnum"="gl"."glnum")
			AND ("csddt"."glrc1"="gl"."glrc1")
	)
	LEFT OUTER JOIN "son_db"."dbo"."ap" "ap" ON
		"csddt"."apnum"="ap"."apnum"

	INNER JOIN "query1" ("csddt"."vo_id" = "query1"."vo_id" AND "csddt"."ckline" = "query1"."voline")

I have NO IDEA what you are trying to here. SQL Server is letting you away with grammer that is totally unreadable.

I don't think you need any of the brackets, I don't think you need ANY of the double quotes.

What did you write the query in? Is this code made up by some tool (hibernate for example)?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jaimealvarez
Starting Member

31 Posts

Posted - 2012-02-07 : 10:50:45

Thank you Charlie. I'm new to sql so still learning on the formatting. Is good to know I don't need those quotes though, because they were driving me crazy.
I got that script from a report that was already built and I was just modifying it to fit what I needed (it was maybe not a good example to learn from).

Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 12:25:24

wont a simple join suffice?


SELECT vo_id, voline,s.num, s.amount, 
STUFF((SELECT ' ' + des FROM son_db.dbo.apvodes WHERE vo_id = t.vo_id AND voline = t.voline ORDER BY dsline FOR XML PATH('')),1,1,'') AS [des]

FROM (SELECT DISTINCT vo_id,voline FROM son_db.dbo.apvodes)t
INNER JOIN son_db.dbo.csddt s
ON s.Vo_ID = t.Vo_ID
AND s.chline = t. voline

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

netraju
Starting Member

4 Posts

Posted - 2012-02-13 : 02:36:00

Hi All,

if the description field contains special characters, then output will be distorted.

vo_id voline des

1 100 AAA

1 200 BBB'data

2 100 CC<DD

2 200 DDD>>FFF<<<Area

2 300 EEE

Check out your queries for this sort of data.
Its better to use pivot here. "xml path" works well with normal characters

Thanks,
Raj Singh
Follow me at http://netraju.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 09:38:07

quote:
Originally posted by netraju

Hi All,

if the description field contains special characters, then output will be distorted.

vo_id voline des

1 100 AAA

1 200 BBB'data

2 100 CC<DD

2 200 DDD>>FFF<<<Area

2 300 EEE

Check out your queries for this sort of data.
Its better to use pivot here. "xml path" works well with normal characters

Thanks,
Raj Singh
Follow me at http://netraju.blogspot.com

use replace to avoid unwanted characters

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-02-13 : 10:31:30

quote:
Originally posted by jaimealvarez

Hi, I've seen a couple of answers to this question but don't seem to work for me. CONCAT_GROUP won't work for me. I'm writing a sql script to use in a crystal report and I need to group multiple lines in one line.

Table: cddts

ID line Description
1 100 TSS
1 200 XTT
2 100 PPT
2 200 TTX
2 300 QPT

So I wil like it to show as:

ID Description
1 TSS XTT
2 PPT TTX QPT

I will appreciate any help. Thanks.

Note that SQL Server does not have any built in function like Mysql has Concat_group function. Also you can use a customised formula field in Crystal report to do the same

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources