pivot

• Deny_Christian

  SSC Eights!

  Points: 914

  More actions

  September 14, 2017 at 11:10 am

  #332575

  dear experts,
  Kindly help solving my problem..

  I want to make the result rows become columnname

  I have syntax like this

  select * from ctlfw_app.dbo.ptbc_schema info
  the results:
  Databasename    Schemaname    Tablename    Columnnames    Columnseq
  DWIM_INP    IN_PROC    INP_1011_1101_PSGL_BU_LEDGER_GROUP    ENABLE_ALTERNATE_ACCOUNT_FLAG    31
  DWIM_INP    IN_PROC    INP_1011_1101_PSGL_BU_LEDGER_GROUP    ACCT_ALT_SUSPENSE    32
  DWIM_INP    IN_PROC    INP_1011_1101_PSGL_BU_LEDGER_GROUP    ALT_ACCT_SUSPENSE    33
  DWIM_INP    IN_PROC    INP_1011_1101_PSGL_BU_LEDGER_GROUP    UPDATE_CLOSING_FLAG    34

  I'd like get the result in column "columnnames" become Columns.

  So I create Pivot:
  
Select
     columnnames
    --select *
FROM CTLFW_APP.dbo.Ptbc_Dwh_Schema_Info as t
Pivot ( Max(t.columnseq)
   For columnseq in ('')
   ) as p
     where TABLEname ='SIM_17_001_ALTFLAG'


  I want the result in "Columnnames" become Column,
  but I Stuck...
  thank you very much

• Deny_Christian

  SSC Eights!

  Points: 914

  More actions

  September 14, 2017 at 11:19 am

  #1959472

  unas_sasing - Thursday, September 14, 2017 11:10 AM

  dear experts,
  Kindly help solving my problem..

  I want to make the result rows become columnname

  I have syntax like this

  select * from ctlfw_app.dbo.ptbc_schema info
  the results:
  Databasename    Schemaname    Tablename    Columnnames    Columnseq
  DWIM_INP    IN_PROC    INP_1011_1101_PSGL_BU_LEDGER_GROUP    ENABLE_ALTERNATE_ACCOUNT_FLAG    31
  DWIM_INP    IN_PROC    INP_1011_1101_PSGL_BU_LEDGER_GROUP    ACCT_ALT_SUSPENSE    32
  DWIM_INP    IN_PROC    INP_1011_1101_PSGL_BU_LEDGER_GROUP    ALT_ACCT_SUSPENSE    33
  DWIM_INP    IN_PROC    INP_1011_1101_PSGL_BU_LEDGER_GROUP    UPDATE_CLOSING_FLAG    34

  I'd like get the result in column "columnnames" become Columns.

  So I create Pivot:
  
Select
     columnnames
    --select *
FROM CTLFW_APP.dbo.Ptbc_Dwh_Schema_Info as t
Pivot ( Max(t.columnseq)
   For columnseq in ('')
   ) as p
     where TABLEname ='SIM_17_001_ALTFLAG'


  I want the result in "Columnnames" become Column,
  but I Stuck...
  thank you very much

  Red: where tablename=' INP_1011_1101_PSGL_BU_LEDGER_GROUP'

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 14, 2017 at 11:28 am

  #1959474

  Read the following articles about dynamically pivoting tables:
  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - SQLServerCentral
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - SQLServerCentral

  The second one explains the dynamic part, but the first one explains the basic process.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Deny_Christian

  SSC Eights!

  Points: 914

  More actions

  September 14, 2017 at 8:10 pm

  #1959532

  Luis Cazares - Thursday, September 14, 2017 11:28 AM

  Read the following articles about dynamically pivoting tables:
  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - SQLServerCentral
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - SQLServerCentral

  The second one explains the dynamic part, but the first one explains the basic process.

  Hai Luis,,
  thank you, i'll read it