?
Solved

Convert rows to columns in SQL Server 2005

Posted on 2009-04-16
11
Medium Priority
?
1,331 Views
Last Modified: 2012-05-06
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!
0
Comment
Question by:ruvik
  • 7
  • 4
11 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24157725
you have to use pivot. have a look at

http://www.sqlhub.com/search?q=pivot+table
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24157987
why you don't want to use aggregate function?

0
 

Author Comment

by:ruvik
ID: 24158118
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24158119
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24158128
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
 

Author Comment

by:ruvik
ID: 24178075
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24178081
what problem you are facing?
0
 

Author Comment

by:ruvik
ID: 24178500
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24178646
well, I don't think so it is possible, even let thread open for other experts.
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24382813
>>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
 

Author Closing Comment

by:ruvik
ID: 31570980
Hi RiteshShah,
I apologize, but only after your objection I noticed your solution posted on 16/04.
Solution accepted. Thanks!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question