How do I create a view where column names are defined from query return values

Question

First, my table layout:

tblEquippedItems: <-(table name)
slotid (FK), itemid (FK), charid (FK) <-(attributes)

So I have this table that describes the items a character has equipped. Slot id refers to the item slot where the item, denoted by itemid resides. Charid describes the character that has these items equipped.

What I'd like to do is present the data in some format like so:

Character Name | Shoulder Item | Head Item | Leg Item | ... | <- Column Names

---

Zalbar | Shoulders of Penance | Helm of Penance | Leggings of Penance | ... | <- Data values

^- This was my feeble attempt to represent a query result set.

I'd like to represent this as a view so I don't have to have an unnecessary and unstable table in the database. The column names will have to come from querying the lookup table that stores the item slot names. Is there a way to programmatically define such a view? Dynamic SQL maybe? I'd love to avoid that...

Answer 1

SELECT
    [character].name                    AS [character_name],
    ISNULL([shoulder].name,'Nothing')   AS [shoulder_item_name],
    ISNULL([head].name,'Nothing')       AS [head_item_name],
    etc...
FROM
   [character]
LEFT JOIN
   [tblEquippedItems] AS [shoulder_item]
      ON [shoulder_item].charid = [character].id
      AND [shoulder_item].slotid = 1
LEFT JOIN
   [item]             AS [shoulder]
      ON [shoulder].id = [shoulder_item].itemid
LEFT JOIN
   [tblEquippedItems] AS [head_item]
      ON [head_item].charid = [character].id
      AND [head_item].slotid = 2
LEFT JOIN
   [item]             AS [head]
      ON [head].id = [head_item].itemid

etc...

Answer 2

What you're looking for in SQL Server 2005/2008 is a Pivot/Cross Tab. You cannot define a View off of this as Views have to be predefined columns. You can do it within a Stored Procedure though.

EDIT: For SQL Server 2000, look at this article about Cross Tabs. Although you'll predominantly see Cross Tabs/Pivots use the Count() or Sum() aggregates, you can just as easily use Max() and Min() on text values.

Answer 3

You're going to get some flack about the database design... a object-attribute-value table like this is considered "evil" for a variety of reasons.

That said, I use them to now and then too when database schemas need to be stable but the final list of attributes is in flux (and when I can live with the performance consequences).

You're at least on the right track by using VIEWs, which will allow you to make the schema more solid as the dust settles.

The bad news: although you can use PIVOT to make your life easier on the SQL side, but the query itself will need to be programmed with the actual column names you want.

There are three ways to do this:

1. Manually modify the VIEWs when you add/remove attributes (headache)
2. Dynamic queries (messy, slower, can't use a VIEW, need a PROCEDURE instead)
3. Create a stored procedure that uses dynamic SQL to drop/create all views after you modify the attributes (most complex)

Answer 4

Lots of self joins are in order i think

    SELECT t1.charid,t1.itemid as shoulders,t2.itemid as helm,t3.itemid as legs
FROM (SELECT charid,itemid from tblEquippedItems where slotid = 1) t1 //slotid = shuolders
INNER JOIN (SELECT itemid from tblEquippedItems where slotid = 2) t2 //slotid = Helm
ON t1.charid = t2.charid
INNER JOIN (SELECT itemid from tblEquippedItems where slotid = 3) t3 //slotid = Legs
ON t3.charid = t2.charid

Other than this, you could try a pivot

Answer 5

When I need to do cross-tabs or pivots, I generally do them outside the database.

I use MS Excel, with a query in it that extracts the data I need, in tabular form. I then pivot in MS Excel to obtain a crosstabulated view with "dynamic column headers".

Excel is far from the only choice. At the top end, you could use something like Cognos data cubes.