Making a T-SQL Query

I have a table like below(bolds are field names)

R_IdNameQ1Q2Q3Q4Q5

M001Mikeabcde

J001 Johnabcde

P001 Peterabcde

I want results based on above table as below. The columns (Q1 to Q5) are put as Question_id for each user like M001 and Question (a, b ,c,d,e ) as column Question exactly as below.

Could anyone help me please writing the script to achieve below from above table.

Result

R_idNameQuestion_idQuestion

M001MikeQ1a

M001MikeQ2b

M001MikeQ3c

M001MikeQ4d

M001MikeQ5e

J001JohnQ1a

J001JohnQ2b

J001JohnQ3c

J001JohnQ4d

J001JohnQ5e

........ ....... ....... ....

Thanks all

If you use sql server 2005,

Code Snippet

Create Table #data (

[R_Id] Varchar(100) ,

[Name] Varchar(100) ,

[Q1] Varchar(100) ,

[Q2] Varchar(100) ,

[Q3] Varchar(100) ,

[Q4] Varchar(100) ,

[Q5] Varchar(100)

);

Insert Into #data Values('M001','Mike','a','b','c','d','e');

Insert Into #data Values('J001','John','a','b','c','d','e');

Insert Into #data Values('P001','Peter','a','b','c','d','e');

Select

R_Id

,Name

,Question_id

,Question

From

#Data

UNPIVOT

(

Question For Question_idin

([Q1],[Q2],[Q3],[Q4],[Q5])

) UPVT

|||

Assuming you are using SQL 2005, you need to use the UNPIVOT operator.

The code will be something like this:

Code Snippet

DECLARE @.MyTable table
( R_Id varchar(10),
Name varchar(20),
Q1 char(1),
Q2 char(1),
Q3 char(1),
Q4 char(1),
Q5 char(1)
)

INSERT INTO @.MyTable VALUES ( 'M001', 'Mike', 'a', 'b', 'c', 'd', 'e' )
INSERT INTO @.MyTable VALUES ( 'J001', 'John', 'a', 'b', 'c', 'd', 'e' )
INSERT INTO @.MyTable VALUES ( 'P001', 'Peter', 'a', 'b', 'c', 'd', 'e' )

SELECT
R_ID,
Name,
Question
FROM @.MyTable
UNPIVOT
( Question FOR Response
IN ( Q1, Q2, Q3, Q4, Q5 )
) unPvt

R_ID Name Question Response
- -- --
M001 Mike Q1 a
M001 Mike Q2 b
M001 Mike Q3 c
M001 Mike Q4 d
M001 Mike Q5 e
J001 John Q1 a
J001 John Q2 b
J001 John Q3 c
J001 John Q4 d
J001 John Q5 e
P001 Peter Q1 a
P001 Peter Q2 b
P001 Peter Q3 c
P001 Peter Q4 d
P001 Peter Q5 e

|||

If you use sql server 2000

Code Snippet

Create Table #data (

[R_Id] Varchar(100) ,

[Name] Varchar(100) ,

[Q1] Varchar(100) ,

[Q2] Varchar(100) ,

[Q3] Varchar(100) ,

[Q4] Varchar(100) ,

[Q5] Varchar(100)

);

Insert Into #data Values('M001','Mike','a','b','c','d','e');

Insert Into #data Values('J001','John','a','b','c','d','e');

Insert Into #data Values('P001','Peter','a','b','c','d','e');

Select R_Id,Name,'Q1' Question_id,[Q1] Question From #Data

Union All

Select R_Id,Name,'Q2' Question_id,[Q2] Question From #Data

Union All

Select R_Id,Name,'Q3' Question_id,[Q3] Question From #Data

Union All

Select R_Id,Name,'Q4' Question_id,[Q4] Question From #Data

Union All

Select R_Id,Name,'Q5' Question_id,[Q5] Question From #Data

|||

Arnie, Missed column...

SELECT
R_ID,
Name,
Response,
Question
FROM @.MyTable
UNPIVOT
( Question FOR Response
IN ( Q1, Q2, Q3, Q4, Q5 )
) unPvt

Make a time frame in T-SQL

Hello

I'm trying to achieve the following in a more clean and dynamically way using a loop or something like that:

print'00:00 00:30 01:00 01:30'

print' |..............................|..............................|..............................|'

The idea is to make a time bar from 00:00 to 23:59.

I'm not sure it can be done with T-SQL...

Has anyone done something like this before?

Thanks!

Worf

The better question is Why try to do this with T-SQL when there are so many BETTER tools for printing.

That really is something you should do in a client application -one that is designed for work with printing.

SQL Server is not designed for printing

|||

I was afraid for an answer like that and I totally agree with you

I'm trying to make a script that gives me an overview of overlapping jobs in SQL Server.
I thought of making it in Reporting Services but what I need is a quick script.

There are two ways:

1. Make something visual and decide for yourself which jobs are overlapping

2. Make the script descide which jobs are overlapping but that might be more complex to visualize it

But thanks anyway

Worf

|||Output your Job Schedule data into Excel or VISIO, or any product that can create something that looks like a PERT chart.