I have a table like this...
| CustomerID | DBColumnName | Data |
| ------------ | ------------ | ------------ |
| 1 | FirstName | Joe |
| 1 | MiddleName | S |
| 1 | LastName | Smith |
| 1 | Date | 12/12/2009 |
| 2 | FirstName | Sam |
| 2 | MiddleName | S |
| 2 | LastName | Freddrick |
| 2 | Date | 1/12/2009 |
| 3 | FirstName | Jaime |
| 3 | MiddleName | S |
| 3 | LastName | Carol |
| 3 | Date | 12/1/2009 |
And I want this...
Is this possible using PIVOT?
| CustomerID | FirstName | MiddleName | LastName | Date |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | Joe | S | Smith | 12/12/2009 |
| 2 | Sam | S | Freddrick | 1/12/2009 |
| 3 | Jaime | S | Carol | 12/1/2009 |
9条答案
按热度按时间w46czmvw1#
yes, but why !!??
llycmphe2#
You can use the MAX aggregate, it would still work. MAX of one value = that value..
In this case, you could also self join 5 times on customerid, filter by dbColumnName per table reference. It may work out better.
j0pj023g3#
41zrol4v4#
Ok, sorry for the poor question. gbn got me on the right track. This is what I was looking for in an answer.
Then I had to use a while statement and build the above statement as a varchar and use dynmaic sql.
Using something like this
Having a to build @fulltext using a while loop and select the distinct column names out of the table. Thanks for the answers.
p8h8hvxi5#
Edit: I have written this without an editor & have not run the SQL. I hope, you get the idea.
piv4azn76#
The OP didn't actually need to pivot without agregation but for those of you coming here to know how see:
sql parameterised cte query
The answer to that question involves a situation where pivot without aggregation is needed so an example of doing it is part of the solution.
6mw9ycah7#
This should work:
kxkpmulp8#
Try this:
p4tfgftt9#
Here is a great way to build dynamic fields for a pivot query:
--summarize values to a tmp table
---see the fields generated