asked on

In SQL 2008 how do I get a Pivot list of columns

I need to list all DISTINCT column names for a list of tables in a Pivot format. I need the column headers to be the actual column names so that I can see which tables are missing the column. Using the first 2 tables, the ChnlngAssess_PTR should have the Question Attempt column (Column 9) as blank. Example below:

table_name      RespID      LTID      UserID      ProjectID      LevelID      SB_Num      CourseAttempt      TestAttempt      QuestionAttempt      QType      Question      RptQuestion      Reference
ARTWBT_PTR      RespID      LTID      UserID      ProjectID      LevelID      SB_Num      CourseAttempt      TestAttempt      QuestionAttempt      QType      Question      RptQuestion      Reference
ChlngAssess_PTR      RespID      LTID      UserID      ProjectID      LevelID      SB_Num      CourseAttempt      TestAttempt            QType      Question      RptQuestion      Reference

There are 78 DISTINCT columns for this group of tables. I have attached the queries that list the tables, columns and order as well as the list of columns.
ColumnName.xlsx
PivotHelp.sql

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

for others:
the spreadsheet is just a colun of column names
& here is the sql from the question

USE CDS;
GO

SELECT  *
FROM    
(
SELECT TOP 100 PERCENT sc.table_schema
					 , sc.table_name
					 , sc.column_name
					 , sc.ordinal_position
from information_schema.columns sc INNER JOIN
sys.tables  AS t ON sc.table_name = t.name INNER JOIN
sys.columns AS c ON c.object_id   = t.object_id AND c.name = sc.Column_Name
WHERE TABLE_NAME LIKE '%_PTR'
order by table_schema, table_name, ordinal_position
)t
PIVOT (MAX(column_name) FOR ordinal_position IN ( [1], 
										          [2],
										          [3],
										          [4],
										          [5],
										          [6],
										          [7],
										          [8],
										          [9],
										          [10],
										          [11],
										          [12],
										          [13],
										          [14],
										          [15],
										          [16],
										          [17],
										          [18],
										          [19],
										          [20],
										          [21],
										          [22],
										          [23],
										          [24],
										          [25],
										          [26],
										          [27],
										          [28],
										          [29],
										          [30],
										          [31],
										          [32],
										          [33],
										          [34],
										          [35],
										          [36],
										          [37],
										          [38],
										          [39],
										          [40],
										          [41],
										          [42],
										          [43],
										          [44],
										          [45],
										          [46],
										          [47],
										          [48],
										          [49],
										          [50],
										          [51],
										          [52],
										          [53],
										          [54],
										          [55],
										          [56],
										          [57],
										          [58],
										          [59],
										          [60],
										          [61],
										          [62],
										          [63],
										          [64],
										          [65],
										          [66],
										          [67],
										          [68],
										          [69],
										          [70],
										          [71],
										          [72],
										          [73],
										          [74],
										          [75],
										          [76],
										          [77],
										          [78]
										  ))p
order by table_name--, ordinal_position

Open in new window

I would much rather see you present a simplified problem and expected result because I think you may be disappointed

Let's say you have this table

TableA
Col1 Col2 Col3
1    1    1
1    2    2
1    2    3

Open in new window

What do you expect as a result? Is it something like this?

TableA
Col1 Col2 Col3
1    1    1
     2    2
          3

Open in new window

How would such a layout answer this: "so I can see which tables are missing the column?"

missing the column?
or missing a particular value in a particular column?
or missing any value from any column?

I'm just not sure you have defined the question sufficiently.

wdbates

ASKER

Hey Scott;

Thanks for the help!

wdbates

ASKER

Thank you Scott, you've always been a great help.

Scott Pletcher

You're welcome! I'm glad it was useful.