How to display the results of my query delimited with pipes?

[WomanPro]

I have this query
select cus.Cust_Code as 'Customer Code',cus.Cust_Name as 'Customer Name',addr.Cadd_address as 'Address',con.Country_Name, par.Param_Descr as 'Level Risk',
case when cus.Cust_Status =0 then 'ACTIVE' else 'InActive' end as 'Customer Status' from TB_CUSTOMER cus
left join TB_CUSTOMER_ADDRESS addr on addr.Cust_Code=cus.Cust_Code
left join TB_COUNTRIES con on con.Country_Id=addr.Country_Id
left join TB_PARAMETERS par on par.Param_Code=cus.Cust_Risklevel
where par.Param_Type='RISKLEVEL'
order by cus.Cust_Code asc

It runs perfectly, it is asked to me to display the results delimited with pipes as follows

56|NIKOLAOS KARELOS|PLASTIRA 17, NEA SMIRNI|GREECE|||SOLONOS 136, ATHENS|GREECE|HIGH|ACTIVE

65|PROFILE SOFTWARE|||SIGGROU 199, NEA SMIRNI|GREECE|SHEIKH AL SAYED RD. 67, DUBAI|UNITED ARAB EMIRATES|LOW|ACTIVE

I have no idea about that, I am new to SQL, may you help me please?
Any help will be much appreciated. Thank you so much in advanced.

 

[Andrzejek]

You may want to read about how to Concatenate SQL Server Columns into a String

Γειά σου!

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

I would also suggest to create another table, something like:

[pre]
TB_STATUS
ST_ID STATUS
0 ACTIVE
1 InActive
[/pre]
so you don't have to have [red]hard-coded data[/red] in your query:[tt]
...
case when cus.Cust_Status = 0 then [red]'ACTIVE'[/red] else [red]'InActive'[/red] end as 'Customer Status'
...[/tt]
Data belongs in tables, not in your code.

You can also ADD other STATUSes to this table, if/when needed

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[WomanPro]

That query was an exercise coding test and I created table as I urged by the exercise.
However if you noticed the result I need it is not only a single pipe
In some values there are three pipes |||
The results I got with the query are 4 lines, so I may have to make the two lines as one but in the specific format?
Do you have any idea?

 

[Andrzejek]

I have noticed your three pipes ||| in some records, and not in others:
[tt]
56|NIKOLAOS KARELOS[red]|[/red]PLASTIRA 17, NEA SMIRNI|GREECE[blue]|||[/blue]SOLONOS 136, ATHENS|GREECE|HIGH|ACTIVE

65|PROFILE SOFTWARE[red]|||[/red]SIGGROU 199, NEA SMIRNI|GREECE[blue]|[/blue]SHEIKH AL SAYED RD. 67, DUBAI|UNITED ARAB EMIRATES|LOW|ACTIVE
[/tt]
What makes some records to have three pipes ||| between certain fields, but the same fields in other record have just one pipe | ?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[WomanPro]

Well that was a coding exercise? I created the query, so that it returns the result, but not at all pipes, so I can't ask about it.
I believe that for ex the first record has 3 pipes after Greece because the values SOLONOS 136, ATHENS|GREECE belong to 56|NIKOLAOS KARELOS
NIKOLAOS KARELOS at PLASTIRA 17 is HIGH and ACTIVE as at SOLONOS 136 too.
To the other record both addresses belong to 65|PROFILE SOFTWARE at SIGGROU 199, NEA SMIRNI|GREECE is LOW and ACTIVE
and at SHEIKH AL SAYED RD. 67, DUBAI|UNITED ARAB EMIRATES| is LOW and ACTIVE too... I have attached you in a screenshot the results.
So I believe that's a simple view they need, he wrote me in the exercise : The result would be as follows (delimited)

56|NIKOLAOS KARELOS|PLASTIRA 17, NEA SMIRNI|GREECE|||SOLONOS 136, ATHENS|GREECE|HIGH|ACTIVE

65|PROFILE SOFTWARE|||SIGGROU 199, NEA SMIRNI|GREECE|SHEIKH AL SAYED RD. 67, DUBAI|UNITED ARAB EMIRATES|LOW|ACTIVE

 

[Andrzejek]

I am sorry, I don’t think I can help you.
Your rule seam to be made based on an arbitrary reason:
“record has 3 pipes after Greece because the values SOLONOS 136, ATHENS|GREECE belong to 56|NIKOLAOS KARELOS”
Your rules should apply to ALL records in your query, not ‘this record should be this, but the next one has its own, unrelated rule’ That will not work.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[WomanPro]

I understand the way you think. I believe it has to do with the view, we don't have to update the records but the way they display.
The exercise sais:

The resultset would be as as follows (delimited):

56|NIKOLAOS KARELOS|PLASTIRA 17, NEA SMIRNI|GREECE|||SOLONOS 136, ATHENS|GREECE|HIGH|ACTIVE

65|PROFILE SOFTWARE|||SIGGROU 199, NEA SMIRNI|GREECE|SHEIKH AL SAYED RD. 67, DUBAI|UNITED ARAB EMIRATES|LOW|ACTIVE

So we don't need at all to update the values of the fields...

 

[Andrzejek]

I do understand what you say, and I do see: "The result set would be as as follows (delimited):", but - that's not requirements/specifications/rules.

You can present these 2 records as an example, but before that - you need to specify the rules. Without knowing the data, you need to explain (to the computer) what needs to happen.

Because you do NOT want to have your SQL say:[pre]
...
Case cus.Cust_Name
When 'NIKOLAOS KARELOS' Then [blue]add one pipe[/blue]
When 'PROFILE SOFTWARE' Then [blue]add three pipes[/blue]
Else [blue]do something crazy[/blue]
End As 'Customer Name'
...[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[WomanPro]

I understand what you're saying. Unfortunately I didn't have any other information about this exercise.
The only thing I can think about right now is that PLASTIRA 17, NEA SMIRNI|GREECE belongs to 56|NIKOLAOS KARELOS, as SOLONOS 136, ATHENS|GREECE too
and SIGGROU 199, NEA SMIRNI|GREECE belongs to 65|PROFILE SOFTWARE as SHEIKH AL SAYED RD. 67, DUBAI|UNITED ARAB EMIRATES too.
So I am not pretty sure if it could be a cretiria for the SQL query about pipes. Just a big curiocity about that.