dynamic columns to rows

Question

I'm trying to do a dynamic conversion of rows to columns.  It shouldn't be this hard but I'm not an SQL expert.  I have 4 columns currently.  I need to convert a variable number of rows to a variable number of columns.  Row "0" = column headings, Row > "0" = row data where columnNo are consecutive.  I don't want any summaries or calculations in the data.  The variables are anywhere from 6 to 15 columns currently, though that could increase to 20.  All the data contains the column it belongs to and the row it belongs to, but I can't figure how to make it display the way I want for use in a crystal report.  The column PartNum is a variable that would be determined by the user at the time of need through a Crystal Report.

I was able to create the result i wanted in Access using a series of queries, but there has to be a better way in SQL.

Again, I'm no expert with SQL and pretty much a beginner.  Precise explanations will be beneficial to me.

Answer 1

You need a PIVOT query. Due to the fact that you're using Access, I would do it in Access, cause it supports "dynamic" columns (SQL Server does only support static, known columns). Here it's called crosstab query.

You need to link your table, then you need to create two queries to normalize your data, thus separating definition of your table from data. The first query extracts the defintion, e.g.

SELECT * FROM EAV WHERE RowNo = 0; 

The second the data:

SELECT EAV.PartNo, EAV.RowNo, Definition.ColumnData AS HeadLine, EAV.ColumnData AS DATA 
FROM EAV 
  INNER JOIN [Definition] ON Definition.ColNo = EAV.ColNo
WHERE RowNo <> 0; 

Now you can use the Query Wizard to build the crosstab on the second query.

btw, your data mode is not the best. You're basically working with an EAV model, but it's not correctly normalized.

Answer 2

The data is in SQL, not Access.  The first view is also a result set, pulling data from 3 different tables.  Maybe it would be better if I showed the layout of those 3 tables first ?
There is also a stored procedure in the SQL application that, when run, shows exactly the column headings I'm looking for, however, they are again listed as rows.