Creating Query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Creating Query

Author

Topic

ashishkukreja
Starting Member

16 Posts

Posted - 2007-08-27 : 02:10:26

I am working in SQL Server 2005

I have one table naming Entry_Form
Where I have columns

ID -------- Name --------- Date ---------- Outlet
1 -------- Gaurav --------- 07/07/2007 ---------- Paradise
2 -------- Manish --------- 05/07/2007 ---------- Taj
3 -------- Gaurav --------- 17/07/2007 ---------- 5 Star
4 -------- Manish --------- 15/07/2007 ---------- Maharani
5 -------- Ashish --------- 18/07/2007 ---------- Green Field
6 -------- Gaurav --------- 19/07/2007 ---------- Paradise
7 -------- Manish --------- 28/07/2007 ---------- Taj
8 -------- Ashish --------- 21/07/2007 ---------- Green Field
9 -------- Ashish --------- 01/07/2007 ---------- Green Field
10 -------- Gaurav --------- 25/07/2007 ---------- 5 Star

Now what I want I required a query where I required a fields like that
Outlet-----------Date1--------Date2---------Date3-----------Date4
Green Field ---18/07/2007---21/07/2007-----01/07/2007-------Null
Taj------------05/07/2007---28/07/2007--------Null----------Null
5 Star---------17/07/2007---25/07/2007--------Null----------Null
Paradise-------07/07/2007---19/07/2007--------Null----------Null
Maharani-------15/07/2007-----Null------------Null----------Null

Something like that please help in this
Please
Thanks
Ashish

You Have to Loss Many Times to Win Single Time

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-27 : 02:20:35

Read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

ashishkukreja
Starting Member

16 Posts

Posted - 2007-08-27 : 02:27:17

Thanks for reply
I will try this

You Have to Loss Many Times to Win Single Time

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 04:09:25

[code]-- Prepare sample data
DECLARE @Sample TABLE (ID INT, Name VARCHAR(6), Date SMALLDATETIME, Outlet VARCHAR(11))

SET DATEFORMAT DMY

INSERT @Sample
SELECT 1, 'Gaurav', '07/07/2007', 'Paradise' UNION ALL
SELECT 2, 'Manish', '05/07/2007', 'Taj' UNION ALL
SELECT 3, 'Gaurav', '17/07/2007', '5 Star' UNION ALL
SELECT 4, 'Manish', '15/07/2007', 'Maharani' UNION ALL
SELECT 5, 'Ashish', '18/07/2007', 'Green Field' UNION ALL
SELECT 6, 'Gaurav', '19/07/2007', 'Paradise' UNION ALL
SELECT 7, 'Manish', '28/07/2007', 'Taj' UNION ALL
SELECT 8, 'Ashish', '21/07/2007', 'Green Field' UNION ALL
SELECT 9, 'Ashish', '01/07/2007', 'Green Field' UNION ALL
SELECT 10, 'Gaurav', '25/07/2007', '5 Star'

-- Stage the data
;WITH Yak (ID, Name, Date, Outlet, RecID)
AS (
SELECT ID,
Name,
Date,
Outlet,
ROW_NUMBER() OVER (PARTITION BY Outlet ORDER BY ID)
FROM @Sample
)

-- Show the expected output
SELECT Outlet,
MAX(CASE WHEN RecID = 1 THEN Date END) AS Date1,
MAX(CASE WHEN RecID = 2 THEN Date END) AS Date2,
MAX(CASE WHEN RecID = 3 THEN Date END) AS Date3,
MAX(CASE WHEN RecID = 4 THEN Date END) AS Date4
FROM Yak
WHERE RecID BETWEEN 1 AND 4
GROUP BY Outlet
ORDER BY Outlet[/code]

E 12°55'05.25"
N 56°04'39.16"

ashishkukreja
Starting Member

16 Posts

Posted - 2007-08-27 : 07:12:57

Thanks brothers for such a nice explanation

Thankyou very much

You Have to Loss Many Times to Win Single Time

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 07:16:19

This staging would be enough

-- Stage the data
;WITH Yak (Date, Outlet, RecID)
AS (
	SELECT	Date,
		Outlet,
		ROW_NUMBER() OVER (PARTITION BY Outlet ORDER BY ID)
	FROM	@Sample
)

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources