Need to Comma Separate Multiple Fields together

[jimoo]

I have data in several fields that need comma separated and combined in one field. I will called the output field MyResults

MyReults will begin with F1 for field01, F2 for field02, and so on.

The data in MyResults should not begin or end with a comma.

Below is code to create the table and sample data. I am looking for assistance to write a query that will produce the data in the MyResults field

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[aaTemp](
	[Field01] [nvarchar](50) NULL,
	[Field02] [nvarchar](50) NULL,
	[Field03] [nvarchar](50) NULL,
	[Field04] [nvarchar](50) NULL,
	[Field05] [nvarchar](50) NULL,
	[Field06] [nvarchar](50) NULL
) ON [PRIMARY]

GO

INSERT into aaTemp Values ('A','','Z','','','X')
INSERT into aaTemp Values ('A','B','','','K','Y')
INSERT into aaTemp Values ('','B','A','D','K','Y')
INSERT into aaTemp Values ('J','I','C','E','F','')
INSERT into aaTemp Values (' ','I','A','E','F','')

View Data.
select * from aaTemp

[!]Attention![/!]
EXPECTED RESULTS

Output 1: F1-A,F3-Z,F6-X
Output 2: F1-A,F2-B,F5-K,F6-Y
Output 3: F2-B,F3-A,F4-D,F5-K,F6-Y
Output 4: F1-J,F2-I,F3-C,F4-E,F5-F
Output 5: F2-I,F3-A,F4-E,F5-F
Output 6:
Output 7: F2-B




Jim

 

[jimoo]

Note:

I missed 2 of the insert statements. Here is record 6 and 7.

INSERT into aaTemp Values (' ','','','','','')
INSERT into aaTemp Values (' ','B','','','','')

Jim

 

[jimoo]

This is close to what I want but I don't want the leading comma when field01 is empty. I can use another script to update the field from postion 2 on if it begins with a comma. It's just not as elegant.

SELECT IIF(field01 = '','','F1-' + field01) 
+ IIF(field02 = '','',',F2-' + field02)
+ IIF(field03 = '','',',F3-' + field03)
+ IIF(field04 = '','',',F4-' + field04)
+ IIF(field05 = '','',',F5-' + field05)
+ IIF(field06 = '','',',F6-' + field06)  as MyResults
from aaTemp

Jim

 

[Mike Lewis]

Jim,

One approach might be to wrap the entire result field in a TRIM(). Something like this perhaps:

SELECT [highlight #FCE94F]TRIM(',' FROM[/highlight] IIF(field01 = '','','F1-' + field01) 
+ IIF(field02 = '','',',F2-' + field02)
+ IIF(field03 = '','',',F3-' + field03)
+ IIF(field04 = '','',',F4-' + field04)
+ IIF(field05 = '','',',F5-' + field05)
+ IIF(field06 = '','',',F6-' + field06)[highlight #FCE94F])[/highlight]  as MyResults
from aaTemp

I haven't tested this, so am not completely sure.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads