SQL View: Display rows as columns? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL View: Display rows as columns?

Author

Topic

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-12-05 : 12:21:48

Hi everyone,

My boss just asked for something that seems a bit impossible. So I have a table that keeps track of all the ISO9001 clauses and Departments associated with them. This table keeps track of what clauses for that department have been covered for the current year.

Here is the Clause_Coverage Table:
Group_ID|Clause_ID|Covered|Year|
---1----|----1----|---1---|2011|
---1----|----2----|---1---|2011|
---1----|----3----|---0---|2011|
---2----|----1----|---0---|2011|
---2----|----2----|---0---|2011|

The [Covered] column is a bit datatype and the others are smallints.

By creating a View with some inner joins and whatnot, I was able to present the data like this:
Year|Group_Name|Covered|----Clauses-----|
2011|Operations|---1---|4.1,4.2,4.3,etc.|
2011|Operations|---0---|4.4,4.5,4.6,etc.|
2011|Accounting|---1---|4.1,4.2,4.3,etc.|
2011|Accounting|---0---|4.4,4.5,4.6,etc.|

Now my boss wants it to look like this:
Department|--4.1--|--4.2--|--4.3--|--4.4--|--4.5--|--4.6--|
Operations|---x---|---x---|---x---|--NC---|--NC---|--NC---|
Accounting|---x---|---x---|---x---|--NC---|--NC---|--NC---|

If it is possible to do this, how would I approach this problem? Do I have to create some sort of temporary table or would a query be sufficient? It seems like I would somehow have to create a bunch of columns based on the number of rows in the Clause table. We use Access 2007 as the front-end and SQL Server 2005 as the back-end, if that helps.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 12:29:32

[code]
SELECT m.Department,
COALESCE(MAX(CASE WHEN n.Val='4.1' THEN 'x'END),'NC') AS [4.1],
COALESCE(MAX(CASE WHEN n.Val='4.1' THEN 'x'END),'NC') AS [4.1],
COALESCE(MAX(CASE WHEN n.Val='4.2' THEN 'x'END),'NC') AS [4.2],
COALESCE(MAX(CASE WHEN n.Val='4.3' THEN 'x'END),'NC') AS [4.3],
COALESCE(MAX(CASE WHEN n.Val='4.4' THEN 'x'END),'NC') AS [4.4],
COALESCE(MAX(CASE WHEN n.Val='4.5' THEN 'x'END),'NC') AS [4.5],
COALESCE(MAX(CASE WHEN n.Val='4.6' THEN 'x'END),'NC') AS [4.6]
FROM
(
SELECT t.Department,
c.Clause
FROM table t
CROSS JOIN Clause c
)m
LEFT JOIN
(
SELECT Department,
f.Val
FROM table p
CROSS APPLY dbo.ParseValues(t.Clauses,',')f
)n
ON n.Department = m.Department
AND n.Val = m.Clause
GROUP BY m.Department
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources