Dynamic pivot for any number of columns and rows

Question

I have the sample device table and data below. I am trying to find a way to transpose or pivot the rows into columns, while adding more devices (rows) and more attributes (columns) over time, while keeping the query intact but I couldn't find a good way to do this. Using SQL Server 2019. (Also wondering if JSON format is a better storage format for what I want to do?)

Desired Output:

                        iPad 2021  iPad mini 2021 ......
price                      329.00          499.00
Releasedate            2011-09-14      2011-09-14
ScreenSize                   10.2             8.3
ScreenResolutionWidth        1620            1488
ScreenResolutionHeight       2160            2266
.....

The values for the attributes can be numeric, string, date, boolean or null.

SQL to create the table and data:

CREATE TABLE [device](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](100) NULL,
    [price] [decimal](18, 2) NULL,
    [Releasedate] [date] NULL,
    [ScreenSize] [decimal](18, 1) NULL,
    [ScreenResolutionWidth] [int] NULL,
    [ScreenResolutionHeight] [int] NULL
 )

SET DATEFORMAT ymd

INSERT INTO [device] (Name, price, Releasedate, ScreenSize, ScreenResolutionWidth, ScreenResolutionHeight) VALUES
('iPad 2021', 329.00, CONVERT(DATETIME, '2011-09-14', 120), 10.2, 1620, 2160),
('iPad mini 2021', 499.00, CONVERT(DATETIME, '2021-09-14', 120), 8.3, 1488, 2266)

Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Thom A, Oct 06 '21 at 07:56
It does not. The question and answer pertain to a fixed # of columns and it's not the same type of layout and it does not address keeping the query the same while being able to handle different # of columns and rows. — Tony_Henrich, Oct 07 '21 at 16:39
The linked dupe isn't for a fixed number of rows though. That's why it's **dynamic**. — Thom A, Oct 07 '21 at 16:44
That's what's happens when you pivot a dynamic number of rows, you end up with a dynamic number of columns... That's what a dynamic pivot does... — Thom A, Oct 07 '21 at 17:42
If your physical table has differingnumber of columns each time you query it then you have a design flaw; table definitions should not be in flux. The only way that could happen is if you are constantly `ALTER`ing the tables definition. Fix your design first, then you likely will find things easier. — Thom A, Oct 07 '21 at 17:50
There's no design flow. New device attributes get added over time. Yes I alter the table over time. So what? That's my use case. I specifically mentioned this in my question and in the title. I suggest you try to understand all the requirements in question before you close a question based on assumptions of yours. You also closed it before I had the chance to reply to your first comment. A query can be used to use the system tables to find out what the current columns are. — Tony_Henrich, Oct 07 '21 at 17:56
If a table's definition changes, you update the queries that reference it; that's normal development cycle. You build the dynamic pivot based on the current definition, and if in the future you need to add a new column you add the column to your queries as well. A dynamic pivot doesn't change that; it's still a query that needs updating. Don't try and make dynamic dynamic queries, that is the path to madness. — Thom A, Oct 07 '21 at 18:00
Never mind. It's too big a hassle to do this in SQL. I will do it in the middle tier. — Tony_Henrich, Oct 07 '21 at 18:09

score 0 · Answer 1 · answered Oct 06 '21 at 06:31

0

Below is the best example of dynamic pivot for any number of columns and rows. After experiencing of many solution if write below one and it is the best. Please transform you query into it and change db name and table also.

declare @col  varchar(500)
select @col = coalesce(@col+',', '') +name FROM  tempdb.sys.columns 
    WHERE object_id = OBJECT_ID('DB_NAME..TABLE_NAME') 

set @sql = 'SELECT RTE_Columns.for_emp_person, EMPNO, RTE_Columns.emp_info_code, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) ser_no,  Columns_code, Data_Value 
FROM   
   (SELECT *
   FROM #COLUMNS_VALUES) p  
UNPIVOT  
   (Data_Value FOR Columns_code IN   
      ('+@col+')  
)AS unpvt
join  RTE_Columns on
unpvt.Columns_code  =   RTE_Columns.desc_code ;';


EXEc sp_executesql   @sql;

answered Oct 06 '21 at 06:31

M Danish

480
2
5

The is a dynamic Unpivot, not pivot. This would turn columns into rows, not the other way round. – Thom A Oct 06 '21 at 07:56
O, I just share you another one. – M Danish Oct 06 '21 at 08:03
it will be helpful to you: https://stackoverflow.com/a/54351133/7480427 – M Danish Oct 06 '21 at 08:09
On top of the fact this doesn't answer the question, there are other issues: selecting from `tempdb` when the table is in `DB_NAME`. Should use `QUOTENAME` for the column names. Should use `FOR XML` or `STRING_AGG` instead of variable coalescing (which is unreliable) – Charlieface Oct 06 '21 at 08:25
2

You know [I've already linked that one](https://stackoverflow.com/questions/69460269/dynamic-pivot-for-any-number-of-columns-and-rows/69460851?noredirect=1#comment122774704_69460269), @MDanish , right? – Thom A Oct 06 '21 at 08:28
This answer does not work and has errors. – Tony_Henrich Oct 07 '21 at 16:42

Dynamic pivot for any number of columns and rows

1 Answers1