1

Possible Duplicate:
Concatenate grouped rows

I am working with this table:

fecha                    cliente    preg  resp  puntos  calif   pagoxejec
2009-03-28 00:00:00 2005   9        23     1      94       5    5
2009-03-28 00:00:00 2005   9        31     1      94       5    5
2009-03-28 00:00:00 2005   9        32     1      55       3    3
2009-03-28 00:00:00 2005   9        33     1      37       2    2
2009-03-28 00:00:00 2005   9        34     0      0        0    0
2009-03-28 00:00:00 2379   7         8     1      0       35    35

(This has more records.)

I have a table called cliente and now this is my query:

DECLARE @supervisor varchar(50)/* sera una cadena de tipo 1 ó   3,4,5 para usarse como supervisor in (@supervisor)*/      
DECLARE @fechainicial datetime /*será la fecha inicial tomada*/      
DECLARE @fechafinal datetime /*será la fecha final tomada*/      
SET @supervisor='1,2,3,4'
SET @fechainicial='2009-02-1 00:00:00.000'
SET @fechafinal='2009-03-19 00:00:00.000'

SELECT smerc.cliente, nivelmkt nivel, MAX(fecha) ultima, preg, resp
FROM smerc
INNER JOIN clientes on smerc.cliente=clientes.cliente
WHERE fecha BETWEEN @fechainicial AND @fechafinal
    AND resp=1
GROUP BY smerc.cliente, preg, resp, nivelmkt
ORDER BY smerc.cliente

And some results:

cliente nivel   ultima          preg resp
 892     1  2009-03-03 00:00:00 2   1
 892     1  2009-03-03 00:00:00 3   1
 892     1  2009-03-03 00:00:00 6   1
 892     1  2009-03-03 00:00:00 23  1
 892     1  2009-03-03 00:00:00 32  1
 892     1  2009-03-03 00:00:00 34  1
 892     1  2009-03-03 00:00:00 44  1
 892     1  2009-03-03 00:00:00 999 1
1889     1  2009-03-03 00:00:00 2   1

The nivel table:

  nivel preg    nomtiny
1   1   E  
1   2   OME
1   3   EX 
1   4   EP 
1   5   E7 
1   6   ROT
1   11  H  
1   12  PPA
1   21  PDV
1   23  CDP
1   31  EXI
1   32  PLC
1   33  COP
1   34  PM 
1   40  O  
1   41  PAP
1   44  NCA
1   999 PRP
2   1   E

Now i need next result:

cliente    nivel    preg    nomtiny
892    1    1   E  
892    1    4   EP 
892    1    5   E7 
892    1    11  H  
892    1    12  PPA
892    1    21  PDV
892    1    31  EXI
892    1    33  COP
892    1    40  O  
892    1    41  PAP

You can see on smerc (BETWEEN @fechainicial AND @fechafinal) this client on the last result doesn't have this spreguntas'records or respuesta=0

(2 condition client doesn't have this preg, (with cliente'nivel) or he does have this record but with resp equal to 0 )

Then my final result must be this:

client  allnomtinyforthisclient
892   E,EP,E7,H,PPA,PDV,EXI,COP,O,PAP

How do I get multiple rows to appear on one line as above?

server is sql 2005

Community
  • 1
  • 1
angel uc
  • 279
  • 2
  • 8
  • 21
  • I'm not 100% sure from your question, but I think you may want to look at the PIVOT command (if you are using SQL SERVER then here: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx) – Paddy Aug 13 '12 at 15:42
  • 2
    @Paddy - This has nothing to do with pivoting. Op seems to want to do a `group-concat`. So, angeluc, what database engine and version are you using?, this has been asked many many times before here – Lamak Aug 13 '12 at 15:45
  • 1
    @angeluc You can use `STUFF` in combination with `FOR XML PATH` to achieve your desired results. Take a look at [these questions](http://stackoverflow.com/search?q=sql+server+xml+path+stuff+concatenate). – Esoteric Screen Name Aug 13 '12 at 16:02
  • @angeluc - The use of `BETWEEN` for ranges (for any type, but **especially** for dates, like here), is _not_ recommended. Always use a 'lower-bound inclusive, upper-bound exclusive' strategy (ie `X >= CURRENT_DATE AND X < CURRENT_DATE + 1 DAY`). Timestamps are particularly difficult to do inclusive upper bounds on. Also, this is a primarily english-speaking site - people may have an easier time understanding your situation if you translate things. And don't include datatype in column names (ie `fechaInicial`). – Clockwork-Muse Aug 13 '12 at 16:33

0 Answers0