0

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.

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

| improve this answer | |

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