By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,397 Members | 1,399 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,397 IT Pros & Developers. It's quick & easy.

how to display single column in different column in sql

P: 1
how to display single column in different column in sql

i am having like this

Expand|Select|Wrap|Line Numbers
  1. DATE        NAME    COUNT
  2. 01-03-2013    JHON    500
  3. 01-03-2013    JONY    2800
  4. 01-03-2013    ANAND    3500
  5. 01-03-2013    RAM    2300
  6. 01-03-2013    VINOD    500
  7. 01-04-2013    JHON    500
  8. 01-04-2013    JONY    2800
  9. 01-04-2013    ANAND    3500
  10. 01-04-2013    RAM    2300
  11. 01-04-2013    VINOD    500
i want to display like

Expand|Select|Wrap|Line Numbers
  1. NAME    OPEING DATE    OPENING COUNT    CLOSING DATE    CLOSING COUNT
  2. JHON    01-03-2013    500            01-04-2013        500
  3. JONY    01-03-2013    2800            01-04-2013        2800
  4. ANAND    01-03-2013    3500            01-04-2013        3500
  5. RAM    01-03-2013    2300            01-04-2013        2300
  6. VINOD    01-03-2013    500            01-04-2013        500
Apr 25 '13 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,430
Is closing date always one day after the opening?
Apr 25 '13 #2

100+
P: 158
Hey maruthu, try this code...

Expand|Select|Wrap|Line Numbers
  1. 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
Apr 29 '13 #3

ck9663
Expert 2.5K+
P: 2,878
Here, tweak this code.

Happy Coding!!!


~~ CK
Apr 29 '13 #4

P: 7
Hi use this qry....
Expand|Select|Wrap|Line Numbers
  1. 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 
  2. A.DATE<=B.DATE
  3. UNION ------------this union is to join same STARTDATE and ENDDATE
  4. SELECT DATE,NAME,COUNT1,DATE FROM TEMPTABLE WHERE NAME IN(
  5. SELECT NAME FROM
  6. (SELECT COUNT(NAME) AS COUNT1,NAME FROM(
  7. SELECT DISTINCT A.DATE AS STARTDATE,A.NAME,A.COUNT1,B.DATE AS ENDDATE FROM TEMPTABLE A
  8. INNER JOIN TEMPTABLE B ON A.NAME=B.NAME ) AS A
  9. GROUP BY NAME) AS D WHERE COUNT1=1)
Regards,
Sri Ganesh
Jun 25 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.