How to join a table with itself in sql?

Question

I have a table - it is like several table concatenated because [Col1] takes two different strings and [Value] takes numerical values relating to [Col1] string. There are 2 sets of columns for segments. Analysis for each segment and later combination of segments. datestamp is also available.

Col1   datestamp Value
ret     1/10/14
ent     1/10/14
ret     2/1/14
ent     2/1/14

Finished table will look like this:

Col1ret  Col2ent  datestamp Value-ret Value-ent
ret       ent     1/10/14
ret       ent     2/1/14

What would be the SQL script to do this?

for any particular date, are there only two ret and ent entries ? — amdixon, Aug 24 '15 at 06:31

score 1 · Answer 1 · answered Aug 24 '15 at 06:40

You can use PIVOT TABLE to convert your query result like this

CREATE TABLE #tmp (col1 nvarchar(5),datestamp DATETIME ,value int)

INSERT INTO #tmp VALUES ('ret',CAST('2014-10-01' AS DATE),null)
INSERT INTO #tmp VALUES ('ent',CAST('2014-10-01' AS DATE),null)
INSERT INTO #tmp VALUES ('ret',CAST('2014-2-1' AS DATE),null)
INSERT INTO #tmp VALUES ('ent',CAST('2014-2-1' AS DATE),null)

SELECT pv.ret
    ,pv.ent
    ,pv.datestamp
    ,pv.value
FROM (
    SELECT t.col1
        ,t.datestamp
        ,t.value
    FROM #tmp t
) as a
PIVOT (
    MAX(col1)
    FOR [col1] IN ([ret],[ent])
) as pv

DROP TABLE #tmp

And you will get Query result like this

    ret ent datestamp               value
-------------------------------------
    ret ent 2014-02-01 00:00:00.000 NULL
    ret ent 2014-10-01 00:00:00.000 NULL

More information see this link below https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

Oh !! I don't see your tag is MySQL I think sql server sorry. — Chanom First, Aug 24 '15 at 06:44
then please see this link http://stackoverflow.com/questions/7674786/mysql-pivot-table — Chanom First, Aug 24 '15 at 06:46

jarlh · Accepted Answer · 2015-08-24T07:10:16.350

1

Just do a simple JOIN:

select t1.Col1 as Col1ret, t2.Col1 as Col2ent, t1.datestamp, t1.value as value-ret, t2.value as value-ent
from tablename t1
  join tablename t2 on t1.datestamp = t2.datestamp
where t1.Col1 = 'ret'
  and t2.Col1 = 'ent'

Since it's MySQL perhaps you need to replace " with back-ticks?

edited Aug 24 '15 at 07:10

answered Aug 24 '15 at 06:40

jarlh

42,561
8
45
63

I am sorry - I updated my question. The Value column is also needs to be divided as illustrated. – user4687194 Aug 24 '15 at 06:50
Suppose if I have 3 additional columns col5, col6 and colD - and colD is also a form of time value as with datestamp, how would your script change? – user4687194 Aug 25 '15 at 04:50

How to join a table with itself in sql?

2 Answers2