• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

Rows To Columns

I have a simple query - Select col1 from table1

I want to convert the rows to columns - is there an easy wasy to do that - am using SQL 2005 if that helps
0
tbaseflug
Asked:
tbaseflug
  • 6
  • 4
1 Solution
 
LowfatspreadCommented:
like this ...

USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID


but you need to know the relevant data values before hand...
0
 
tbaseflugAuthor Commented:
somewhat works - except I do not want to return the count of the values currently being returned as rows but rather take:
Cols
--------
val1
val2
val3

and bring back something like:
col1  col2  col3
-----  -----  -----
val1  val2  val3
0
 
Salim FayadCommented:
You have to use an "Aggregate function". So, if your values "val1", "val2", ... are numeric and you want to display them as they are, you can use "SUM" in place of "COUNT" and you will have them as they are (for example, if val1=39, val2 = 43, and val3=54, then you will have them as they are since each one will be summing with it self, the result will be:
col1  col2  col3
----   -----  -----
39     43     54
).
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
tbaseflugAuthor Commented:
What if the values are non-numeric
0
 
Salim FayadCommented:
What are the values? Strings?

If so, then you have 2 solutions:
1. Create a temporary table that has your columns (your data as columns) and fill it. You have to use the cursor in this case or a functionality similar to the cursor (creating another temporary table and getting the top row and deleting this row)
2. Create your own aggregate function that returns your string values as strings as they are
0
 
tbaseflugAuthor Commented:
The values are strings, e.g.:
Values
-----------
Position
Title
AFE
Rate Code

What I want is:
Col1       Col2  Col3  Col4
Position  Title  AFE  Rate Code  
0
 
Salim FayadCommented:
This means that you have to follow one of the 2 solutions that I previously described.
0
 
tbaseflugAuthor Commented:
Any examples of somthing like that?
0
 
Salim FayadCommented:
This is an example on the first solution, where you have 2 tables:
1. LKUP_Subject that has 2 columns: Id, Description_En (it represents the name of the subject)
2. Student_Subject which is a M-N relation between Student table and LKUP_Subject table, it has 4 columns:
    a. Id: PK
    b. StudentId: FK
    c. SubjectId: FK
    d. Grade

It creates a temporary table that has the columns that you want, and then fills them based on the SubjectId in the Student_Subject table. Check it, and if you need any help, please let me know. I will send a link regarding the second solution, but this one is better. And note that I didn't use any aggregate function.

This is the example:

DECLARE Cur CURSOR FOR
      SELECT DISTINCT id, Description_En
      FROM LKUP_Subject

DECLARE @SubjectName NVARCHAR(MAX),
            @SubjectId INT,
            @TempTable NVARCHAR(MAX),
            @Select NVARCHAR(MAX)

SET @TempTable = 'CREATE TABLE ##TempSubject('
SET @Select = 'INSERT INTO ##TempSubject SELECT '

OPEN Cur

FETCH NEXT FROM Cur INTO @SubjectId, @SubjectName
WHILE @@FETCH_STATUS = 0
BEGIN
      SET @TempTable = @TempTable + '[' + @SubjectName + '] NVARCHAR(100),'      
      SET @Select = @Select + '(CASE WHEN SubjectId = ' + CONVERT(NVARCHAR(10), @SubjectId) + ' THEN Grade END),'
      FETCH NEXT FROM Cur INTO @SubjectId, @SubjectName
END

CLOSE Cur
DEALLOCATE Cur


IF RIGHT(@TempTable, 1) = ','
      SET @TempTable = STUFF(@TempTable, LEN(@TempTable), 1, ')')

IF RIGHT(@Select, 1) = ','
      SET @Select = STUFF(@Select, LEN(@Select), 1, ' FROM Student_Subject')

EXEC sp_executesql @TempTable
print @Select
EXEC sp_executesql @Select

SELECT * FROM ##TempSubject
DROP TABLE ##TempSubject
0
 
Salim FayadCommented:
Regarding creating a new aggregate function, this link is very descriptive:

http://weblogs.asp.net/dwahlin/search.aspx?q=aggregate&o=Relevance
0
 
Salim FayadCommented:
What happened with you tbaseflug?
If my solutions didn't help you, let me know in order to help you. If they worked fine, then don't foget to accept my solutions.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now