0

This question already has an answer here:

I havent worked with Pivot. Not sure if pivot is the right thing to use. Is it possible to show this data:

-------------------
| Name     | Code |
-------------------
|Test A    | 1    |
|Test B    | 2    |
|Test C    | 3    |
-------------------

In this format:

------------------------------
| Customer | Fleet | Account |
------------------------------
|Test A    |Test B |Test C   |
------------------------------

If Code is 1 it is Customer. If Code is 2 it is Fleet. If Code is 3 it is Account.

marked as duplicate by Paul White sql-server Jun 14 '18 at 5:39

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2

You can accomplish this with pivot, when you are dealing with translating the columns into rows you will still need to utilize an aggregate. The pivot has three primary pieces:

  1. Group
  2. Spread
  3. Aggregate

DECLARE @table TABLE (name VARCHAR(255), code INT);

INSERT INTO @table
VALUES ('Test A', 1), ('Test B', 2), ('Test C', 3);

SELECT [1] AS 'Customer', [2] AS 'Fleet', [3] AS 'Account'
FROM @table
PIVOT(MAX(name) FOR code IN (
            [1],
            [2],
            [3]
            )) pvt;

In the working example above we see the grouping as Name and we spread the results of code across three columns with hard coded values. The aggregate is based on the MAX value of name, this is what let's us pivot the row by pivoting on a group with a max value that would only return one row.

Now if you want to create this dynamically vs hardcoding, you'll need to look into dynamic sql.

Here's an article on dynamic pivot sql on MS SQL Tips by Aaron Bertrand

Not the answer you're looking for? Browse other questions tagged or ask your own question.