how to display single column in different column in sql

[maruthu]

how to display single column in different column in sql

i am having like this

DATE		NAME	COUNT
01-03-2013	JHON	500
01-03-2013	JONY	2800
01-03-2013	ANAND	3500
01-03-2013	RAM	2300
01-03-2013	VINOD	500
01-04-2013	JHON	500
01-04-2013	JONY	2800
01-04-2013	ANAND	3500
01-04-2013	RAM	2300
01-04-2013	VINOD	500

i want to display like

NAME	OPEING DATE	OPENING COUNT	CLOSING DATE	CLOSING COUNT
JHON	01-03-2013	500			01-04-2013		500
JONY	01-03-2013	2800			01-04-2013		2800
ANAND	01-03-2013	3500			01-04-2013		3500
RAM	01-03-2013	2300			01-04-2013		2300
VINOD	01-03-2013	500			01-04-2013		500

[Rabbit]

Is closing date always one day after the opening?

[vijay6]

Hey maruthu, try this code...

SELECT TOP (SELECT COUNT(DISTINCT Name) FROM Table_1) Name, D2 AS "Opening Date", N2 AS "Opening Count", D1 AS "Closing Date", N1 AS "Closing Count" FROM (SELECT T1.Name, T1.Datee AS "D1", T1.Num AS "N1", T2.Datee AS "D2", T2.Num AS "N2" FROM Table_1 T1, Table_1 T2 WHERE T1.Name = T2.Name AND T1.Datee <> T2.Datee) AS T3

[ck9663]

Here, tweak this code.

Happy Coding!!!


~~ CK

[Sri Ganesh]

Hi use this qry....

SELECT A.DATE,A.NAME,A.COUNT1,B.DATE FROM TEMPTABLE A,TEMPTABLE B WHERE A.NAME=B.NAME AND A.DATE!=B.DATE AND 
A.DATE<=B.DATE
UNION ------------this union is to join same STARTDATE and ENDDATE
SELECT DATE,NAME,COUNT1,DATE FROM TEMPTABLE WHERE NAME IN(
SELECT NAME FROM
(SELECT COUNT(NAME) AS COUNT1,NAME FROM(
SELECT DISTINCT A.DATE AS STARTDATE,A.NAME,A.COUNT1,B.DATE AS ENDDATE FROM TEMPTABLE A
INNER JOIN TEMPTABLE B ON A.NAME=B.NAME ) AS A
GROUP BY NAME) AS D WHERE COUNT1=1)

Regards,
Sri Ganesh