0

enter image description here

Please help me get the data from the input table in the form of an output table using SQL. There are more than 60 status codes in data so a query which is reproducible is desire. A query that automatically transforms the 60 status codes to columns.

Currently, I am using the below query but with this query, the problem is if a new status code is created it needs to be updated Can you please give me a solution to automate this query if a new status code is introduced.

WITH PivotData AS
(
SELECT 
       [acctrefno]
      ,LSC.status_code
      ,1  AS Qty
  FROM [NLS].[dbo].[loanacct_statuses] LS
  INNER JOIN loan_status_codes LSC ON LSC.status_code_no = LS.status_code_no
)
SELECT acctrefno, [PIF], [CUR], [DUE], [WCC]
FROM PivotData
PIVOT( SUM(Qty) FOR Status_code IN([PIF], [CUR], [DUE], [WCC])) AS P
Dale K
  • 25,246
  • 15
  • 42
  • 71
Shrey
  • 21
  • 7
  • 2
    You want a dynamic pivot with unknown number of columns. See if this helps: https://stackoverflow.com/questions/213702/sql-server-2005-pivot-on-unknown-number-of-columns – SS_DBA Feb 12 '20 at 18:27
  • 1
    How many times are you going to ask the same question? And please post sample data as text. Images are nearly worthless. [Why?](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) – Sean Lange Feb 12 '20 at 19:30
  • Does this answer your question? [I want to pivot the data in a table in sql server database in a binary transformation example given below](https://stackoverflow.com/questions/60192921/i-want-to-pivot-the-data-in-a-table-in-sql-server-database-in-a-binary-transform) – VBoka Feb 12 '20 at 20:54

0 Answers0