2

I'm having trouble with a SQL query and I was wondering if any one of you could use a little of your time to help me.

I have the following query

SELECT 
cfd_enc.id,
cfd_enc.version,
cfd_enc.prefijo,
cfd_enc.fecha,
cfd_enc.cfd,
cfd_enc.no_aprobacion,
cfd_enc.tipo_comprobante,
cfd_det.id_orden,
cfd_det.producto,
cfd_det.total
FROM cfd_parametros,cfd_enc LEFT JOIN cfd_det ON cfd_enc.id=cfd_det.id_orden

which gets me this:


(I have 6 rows in cfd_det that share id_orden with cfd_enc.id)

Is there any way I could combine all the repeated rows to get something like this?

Any help is really appreciated. I'm really stuck, so let me know if you need more specific information.

Qix - MONICA WAS MISTREATED
  • 14,451
  • 16
  • 82
  • 145
Isracg
  • 75
  • 3
  • 9
    You really want what you get, you just dont know why yet ;) – stefan Mar 07 '11 at 22:00
  • Tip: You could directly include those Jpgs in the question. – rkg Mar 07 '11 at 22:01
  • I'd agree with stefan. What you got in your query is likely what you want. The second output makes no sense for many reasons not the least of which you have repeated column names. – Thomas Mar 07 '11 at 22:03
  • @Isracg I agree with stefan. Maybe we can help you with the "Why" if you explain why you desire the results be combined. – Matthew Cox Mar 07 '11 at 22:03
  • there could be a way if there are always N products. But you cannot build a query that will have an arbitrary number of columns – RichardTheKiwi Mar 07 '11 at 22:03
  • What programming language are you using? What do you want to do with that data? – mellamokb Mar 07 '11 at 22:03
  • @Richard: Technically, you can with Dynamic SQL. However, it doesn't seem like the best solution. – mellamokb Mar 07 '11 at 22:04
  • You could do a GROUP BY for every column to reduce the number of rows, but all the values have to be the same. In your case producto and total are different, so in the end you'd have the same result set unless you remove those. – Thyamine Mar 07 '11 at 22:04
  • The why is, it would be easier for me to read that data, instead of having several rows with repeated info in most columns :) – Isracg Mar 07 '11 at 22:06
  • @mellamokb Can't you tell by the field names? He's using `Le ŜQL` – George Johnston Mar 07 '11 at 22:07
  • I can't see the difference between both representations when traversing the data, apart from less space consumption of course. At first glance it doesn't matter if you are exploring rows or columns. – elitalon Mar 07 '11 at 22:08
  • I guess it would be easier for me to read the data the way i wanted to, but if there's no easy way to do that guess i'm reading multiple columns hehe. – Isracg Mar 07 '11 at 22:11
  • @George: Lol. I meant, where does this data go afterward? A `PHP` page? `ASP.Net`? `Microsoft Excel`? Printer output? Just reading it on the screen? – mellamokb Mar 07 '11 at 22:11
  • @mellamokb: Display it in a windows form using C#. – Isracg Mar 07 '11 at 22:13
  • @Isracg: In that case, it might be easier to format the display of the data on the C# side instead of the SQL side. – mellamokb Mar 07 '11 at 22:15
  • Thanks for all of your help, i'm gonna format the data on the C# side instead, as mellamokb recommended :) – Isracg Mar 07 '11 at 22:21

1 Answers1

5

Good question - this type of problem actually comes up quite often. It looks like what you're trying to do is 'pivot' rows into columns. Similar questions have been asked/answered elsewhere on SO:

SQL Server 2005, turn columns into rows

How to transform rows to columns

Community
  • 1
  • 1
drapkin11
  • 1,205
  • 2
  • 12
  • 24