Delete repeated fields sql

Question

Delete repeated fields sql

Navigation

#1 by (0 votes)

2

Could someone give me a hand with my code?

select nro_viaje 
into #vviajes
from [ARROSWKS604\SQLEXPRESS].[Test].[dbo].[Hoja1$]

select nro_viaje, cod_cliente
into #cclientes
from [ARROSWKS604\SQLEXPRESS].[Test].[dbo].[Hoja1$]

Select distinct ST2.cod_cliente,
substring(
    (
        Select '-'+convert(varchar,ST1.cod_cliente)  AS [text()]
        From #cclientes ST1
        Where ST1.nro_viaje = ST2.nro_viaje
        ORDER BY ST1.cod_cliente
        For XML PATH ('')
    ), 2, 1000) COMBINABLES
,(SELECT count(*) FROM #vviajes TMP2 WHERE ST2.nro_viaje = TMP2.nro_viaje GROUP BY nro_viaje)
From #cclientes ST2

The first column would show all the "customer_codes". What I try to do is show each of the "customer_codes" that made the same trip called "nro_viaje", but without the "customer_code" being repeated in the COMBINABLE field.

This is the result I get:

As I mentioned, the value of the column "customer_code" is repeated in COMBINABLES, which is what I do not want to appear.

Any help is useful!

sql-server

asked by Bruno Furiasse 27.04.2016 в 22:01

source

1 answer

Use AndroidStudio in AMD processor to run and debug an app facebook login sdk php Error: 'URL blocked: This redirect failed because ...'

score 0 · Accepted Answer

Difficult to try without having the table, but tried with Distinct (changing the text by nvarchar) or gruop By ?. You have to change the data type from Text to Nvarchar because with Text you can not do neither Distinct nor Group By.

Distinct

select nro_viaje 
into #vviajes
from [ARROSWKS604\SQLEXPRESS].[Test].[dbo].[Hoja1$]

select nro_viaje, cod_cliente
into #cclientes
from [ARROSWKS604\SQLEXPRESS].[Test].[dbo].[Hoja1$]

Select distinct ST2.cod_cliente,
substring(
    (
        Select distinct  '-'+convert(varchar,ST1.cod_cliente)  AS [nvarchar(max)]
        From #cclientes ST1
        Where ST1.nro_viaje = ST2.nro_viaje

        ORDER BY ST1.cod_cliente
        For XML PATH ('')
    ), 2, 1000) COMBINABLES
,(SELECT count(*) FROM #vviajes TMP2 WHERE ST2.nro_viaje = TMP2.nro_viaje GROUP BY nro_viaje)
From #cclientes ST2

Group BY

select nro_viaje 
into #vviajes
from [ARROSWKS604\SQLEXPRESS].[Test].[dbo].[Hoja1$]

select nro_viaje, cod_cliente
into #cclientes
from [ARROSWKS604\SQLEXPRESS].[Test].[dbo].[Hoja1$]

Select distinct ST2.cod_cliente,
substring(
    (
        Select '-'+convert(varchar,ST1.cod_cliente)  AS [nvarchar(max)]
        From #cclientes ST1
        Where ST1.nro_viaje = ST2.nro_viaje
        GROUP BY '-'+convert(varchar,ST1.cod_cliente)
        ORDER BY ST1.cod_cliente
        For XML PATH ('')
    ), 2, 1000) COMBINABLES
,(SELECT count(*) FROM #vviajes TMP2 WHERE ST2.nro_viaje = TMP2.nro_viaje GROUP BY nro_viaje)
From #cclientes ST2