SQL Query

Question

1

Answer 1

Hi Everybody,

I have a table and some fields and data like this.

Thanks in advance.

Sunil

Answer 2

So what is the problem?

---

Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

Answer 3

Hello Sunil,

Please avoid double posts.

Looks like pivoting, see Using PIVOT and UNPIVOT

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 4

thanks Olaf for merging both the threads

---

Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

Answer 5

You can either use an aggregate function along with CASE statement or a PIVOT operator like below;

DECLARE @Attendance AS TABLE
(
	Name VARCHAR(10),
	AttendanceStatus    VARCHAR(10),
	AttendanceDate DATETIME
)

INSERT INTO @Attendance (Name, AttendanceStatus, AttendanceDate) VALUES
	('AA', 'P', '01/11/2014'),
	('BB', 'A', '01/11/2014'),
	('CC', 'E', '01/11/2014'),
	('AA', 'A', '02/11/2014'),
	('BB', 'P', '02/11/2014')

-- Method #1	
SELECT
	Name
	,MAX(CASE WHEN AttendanceDate = '01/11/2014' THEN AttendanceStatus
		ELSE NULL END) AS [01/11/2014],
	MAX(CASE WHEN AttendanceDate = '02/11/2014' THEN AttendanceStatus
		ELSE NULL END) AS [02/11/2014]
FROM @Attendance
GROUP BY Name

-- Method #2
SELECT Name, [01/11/2014], [02/11/2014]
FROM
	(SELECT Name, AttendanceStatus, AttendanceDate
		FROM @Attendance) AS SourceTable
PIVOT
(
	MAX(AttendanceStatus)
	FOR AttendanceDate IN ([01/11/2014], [02/11/2014])
) AS PivotTable;

Read more on PIVOT operator here (Using PIVOT and UNPIVOT).

Or you may need a dynamic pivot for various dates. Read the below article to get an idea on dynamic pivot;

Script to create dynamic PIVOT queries in SQL Server

---

Krishnakumar S