1

I've used pivot before but I can't figure out how to group by 2 columns and pivot while dynamically creating column names.

I have a table with data that's grouped by 2 columns, "unit" and "grouped" in my example below. Each "unit" contains unique data and within each "unit" the data is further grouped and I want to turn this column data into rows for reporting purposes. There are also a fieldName column that indicates what each value is, however the field names are not static.

Sample table:

CREATE TABLE #test (
  fieldID int ,
  fieldName nvarchar(50),
  unit nvarchar(50),
  grouped int,
  value varchar(255)
);
INSERT INTO #test (fieldID, fieldName, unit, grouped, value) VALUES (1, 'uid',      'unit 1', 1, 'bob');
INSERT INTO #test (fieldID, fieldName, unit, grouped, value) VALUES (2, 'name',     'unit 1', 1, 'bob');
INSERT INTO #test (fieldID, fieldName, unit, grouped, value) VALUES (3, 'policy',   'unit 1', 1, '123456');
INSERT INTO #test (fieldID, fieldName, unit, grouped, value) VALUES (4, 'adress',   'unit 1', 1, '123 Some st.');
INSERT INTO #test (fieldID, fieldName, unit, grouped, value) VALUES (1, 'uid',      'unit 1', 2, 'willie');
INSERT INTO #test (fieldID, fieldName, unit, grouped, value) VALUES (2, 'name',     'unit 1', 2, 'william');
INSERT INTO #test (fieldID, fieldName, unit, grouped, value) VALUES (3, 'policy',   'unit 1', 2, '45678');
INSERT INTO #test (fieldID, fieldName, unit, grouped, value) VALUES (4, 'adress',   'unit 1', 2, '456 some other st.');
INSERT INTO #test (fieldID, fieldName, unit, grouped, value) VALUES (5, 'nickname', 'unit 1', 2, 'Bill');
INSERT INTO #test (fieldID, fieldName, unit, grouped, value) VALUES (1, 'uid',      'unit 2', 1, 'djoe');
INSERT INTO #test (fieldID, fieldName, unit, grouped, value) VALUES (2, 'name',     'unit 2', 1, 'jane');

Is there a way to pivot this:

fieldID fieldName unit grouped value
1 uid unit 1 1 1
2 name unit 1 1 bob
3 policy unit 1 1 123456
4 adress unit 1 1 123 Some st.
1 uid unit 1 2 willie
2 name unit 1 2 william
3 policy unit 1 2 45678
4 adress unit 1 2 456 some other st.
5 nickname unit 1 2 Bill
1 uid unit 2 1 djoe
2 name unit 2 1 jane

into this so that the groupings on unit/grouped are performed and the column names in the output table are dynamically generated based on the values in fieldName?

Like this:

unit uid name policy address nickname
unit 1 bob bob 123456 123 Some St.
unit 1 willie william 45678 456 some other st. Bill
unit 2 jdoe jane

EDIT

This appears to do the grouping, but are the dynamic column names possible?

SELECT * FROM(
  SELECT [fieldName], [unit], [grouped], [value] FROM #test
) t
PIVOT(
  MIN([value])
  FOR fieldName IN([uid], [name], [policy], [adress], [nickname])
) as pivot_table;
user3246693
  • 679
  • 11
  • 22
  • not in straight sql. you need to use a stored procedure or script to create the SQL statement as a string in a variable, then execute it – Kurt Apr 28 '22 at 20:49
  • For dynamic column names, you need dynamic SQL. [Start here](https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/?utm_source=AaronBertrand). – Aaron Bertrand Apr 28 '22 at 20:51
  • Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Thom A Apr 28 '22 at 21:00

0 Answers0