Convert rows to columns in SQL Server 2005

Hi,

I need to convert rows to columns, much like the PIVOT command, but without an aggregate function.

I have two tables that holds an invoices data::

This table holds only the available fields in an invoice. it does not hold any actual invoice data:
CREATE TABLE [dbo].[InvoicesFields](
      [CustomerCode] [int] NOT NULL,
      [FieldCode] [smallint] NOT NULL,
      [Description] [nvarchar](50) COLLATE SQL_Latin1_General_CP1255_CI_AS NOT NULL,
 CONSTRAINT [PK_InvoicesFields] PRIMARY KEY CLUSTERED
(
      [CustomerCode] ASC,
      [FieldCode] ASC,
)

for example, if an invoice for customer 123 has 2 fields, invoice date and invoice number then the table has the following 2 rows:
123,1,Invoice Date
123,2,Invoice Number

The second table holds the actual data of the invoice:
CREATE TABLE [dbo].[InvoicesFieldsData](
      [CustomerCode] [int] NOT NULL,
      [InvoiceCode] [bigint] NOT NULL,
      [FieldCode] [smallint] NOT NULL,
      [FieldData] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1255_CI_AS NOT NULL
) ON [PRIMARY]

If I have one invoice with code=1001, invoice date=1/1/1980, invoice number=56789 then I will have the following 2 rows in the table:
123, 1001, 1, 1/1/1980
123, 1001, 2, 56789

Now, what I need is a sql that will return the fieldcode as columns. lets say I have only 2 field codes: 1=invoicedate, 2=invoicenumber, I need a table that looks like that:
table columns:
customercode, invoicecode, 1, 2
table rows:
123, 1001, 1/1/1980, 56789

Is that possible?
Thanks in advance!
ruvikAsked:
Who is Participating?
 
RiteshShahCommented:
>>I am not facing any problem. Its just that i dont need to use any aggregate function like sun/avg/count etc. I just need the plain data.<<

you needed PIVOT data and I saw you the way, not possible to do PIVOT without aggregate function and that is fact.


here is your another comment.

>>How can I use an aggregate function? i do not need to sum anything or to get max or avg of anything. I just need the data.
If there is a way to Pivot it with an aggregate function I would use it. Can you please show me how?<<

you clearly mantioned that you want to see how you can pivot on string data with aggregate and I gave you example which is working indeed. and you don't have any problem in data return from that, you have admitted.
0
 
RiteshShahCommented:
you have to use pivot. have a look at

http://www.sqlhub.com/search?q=pivot+table
0
 
RiteshShahCommented:
why you don't want to use aggregate function?

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ruvikAuthor Commented:
How can I use an aggregate function? i do not need to sum anything or to get max or avg of anything. I just need the data.
If there is a way to Pivot it with an aggregate function I would use it. Can you please show me how?
0
 
RiteshShahCommented:
can't this work?

--pivot query
SELECT * from (
select [CustomerCode],[InvoiceCode],[FieldCode],[FieldData] FROM [InvoicesFieldsData]
) up
PIVOT (min([FieldData]) for [FieldCode]in ([1],[2])) AS pivo

you can have dynamic pivot as well but this is again with aggregate function.
0
 
RiteshShahCommented:
see above query is catering your need, now if you wish to make it dynamic, you can do it, have a look at my article

http://www.sqlhub.com/2009/03/dynamic-pivot-with-where-condition-in.html
0
 
ruvikAuthor Commented:
Thanks RiteshShah, but your query also uses an aggregate function like sum, and I cant use an aggregate function. i cant see this working if I have to use an aggregate function.
0
 
RiteshShahCommented:
what problem you are facing?
0
 
ruvikAuthor Commented:
I am not facing any problem. Its just that i dont need to use any aggregate function like sun/avg/count etc. I just need the plain data.
0
 
RiteshShahCommented:
well, I don't think so it is possible, even let thread open for other experts.
0
 
ruvikAuthor Commented:
Hi RiteshShah,
I apologize, but only after your objection I noticed your solution posted on 16/04.
Solution accepted. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.