SQL Server Performance Forum – Threads Archive

Creating a view with dynamic rows.

I know this is may not fall under the category of T-SQL tuning, but I can not find a better place for sql-server knowlegde. Ok, basicly there is three tables:
ITEM, SESSION AND ITEM_RESPONSE (CODE BELOW):
ITEM stores information about items, an item is basicly a question.
SESSION stores information about a spesific user session.
ITEM_RESPONSE stores the question response of a session to a spesific ITEM What i need is a way to get all this information out in a view. I am looking for a view with a resultset which would in the first column show session_sid’s and then add a new column for each item and naming the column the same as the itemname. The column order must be with session_sid first and then the itemnames ordered ascending.
Then I would have one new row for each session. SESSION_SID, RESPONSE ITEM 1, RESPONSE ITEM 2, …. -> RESPONSE ITEM X At this moment I have built a javaclass which creates this for me, but I’m wondering if it is possible and better to do it directly in the database? Create table [dbo].[ITEM] (
[ITEM_SID] Integer NOT NULL,
[NAME] Varchar(30) NULL,
[ACTIVE] Varchar(10) NULL,
[FASIT] Varchar(10) NULL,
[TEXT] Varchar(2000) NULL,
[ALT_PACKAGE_SID] Integer NULL,
[HEAD_TEXT] Varchar(500) NULL,
[ALTERNATIVES_DIRECTION] Varchar(100) NULL,
[VALIDATION_METHOD] Varchar(30) NULL,
[INTRODUCTION] Integer NULL,
Primary Key ([ITEM_SID])
)
go Create table [dbo].[SESSION] (
[SESSION_SID] Integer NOT NULL,
[COMPLETED] Varchar(10) NULL,
[START_DATE] Datetime NULL,
[END_DATE] Datetime NULL,
[RESUMES] Varchar(10) NULL,
[TOTAL_USED_TIME] Varchar(100) NULL,
[LAST_QST_SID] Integer NULL,
[RUN_ID] Integer NOT NULL,
Primary Key ([SESSION_SID])
)
go Create table [dbo].[ITEM_RESPONSE] (
[SESSION_SID] Integer NOT NULL,
[ITEM_SID] Integer NOT NULL,
[TIMESTAMP] Datetime NULL,
[RESPONSEVALUE] Varchar(2000) NULL,
[SCOREVALUE] Varchar(50) NULL,
[TDIFF_KEYEVENT] Integer NULL,
[TDIFF_SUBMIT] Integer NULL,
Primary Key ([SESSION_SID],[ITEM_SID])
)
go
Alter table [dbo].[ITEM_RESPONSE] add foreign key([ITEM_SID]) references [ITEM] ([ITEM_SID])
go
Alter table [dbo].[ITEM_RESPONSE] add foreign key([SESSION_SID]) references [SESSION] ([SESSION_SID])
go

---

Presentation issue. The ever popular crosstab report. Done at least a hundred times here. Also explained in BOL.

---

By the way, AFAIK a view can’t handle dynamic query statements.

---

Correct. A view can’t "use" dynamic SQL. If you decide to go that way, you need an stored procedure. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

As Adriaan mentioned, javaclass is the right place for implementation of such functionality. In other words, better do it outside of database.

---

If you use front end, then it is correct time to say<br />Do this in front end application [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

---

]]>