CODEWITHSUNDEEP
sqlsql-servergroup-bypivot
Mildfire
Mildfire

Reputation: 323

Difficult Grouping Query

I am having trouble writing a query to get the results I want. I have the following table:

CREATE TABLE [dbo].[inputs](
    [iid] [int] IDENTITY(1,1) NOT NULL,
    [tag_id] [int] NULL,
    [date_time] [datetime] NULL,
    [input_raw] [float] NULL,
    [input_calibrated] [float] NULL,
    [input_type] [varchar](50) NULL,
    [virtual_record] [varchar](50) NULL,
    [status_change] [varchar](50) NULL,
    [prev_stat_chg] [varchar](50) NULL,
    [prev_status_change] [varchar](50) NULL,
    [unix_timestamp] [float] NULL,
 CONSTRAINT [PK_inputs] PRIMARY KEY CLUSTERED 
(
    [iid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,   ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]`
)

I am writing a query to get the data in a format which can be used with a timeline graph that I have. Needing the data "Grouped By" "unix_timestamp" with "n" number of tag_id's "input_raw".

For example:

SELECT TOP 20 inputs.unix_timestamp, inputs.input_raw, inputs.tag_id 
FROM [200030].[dbo].inputs
WHERE inputs.tag_id = 92149 or inputs.tag_id = 92164
ORDER BY unix_timestamp DESC

Gives me the results:

unix_timestamp  input_raw   tag_id
1357788990  313         92149
1357788990  210         92164
1357788690  313         92149
1357788690  210         92164
1357788390  313         92149
1357788390  210         92164
1357788090  313         92149
1357788090  210         92164
1357787790  313         92149
1357787790  210         92164
1357787490  313         92149
1357787490  210         92164
1357787190  313         92149
1357787190  210         92164
1357786890  313         92149
1357786890  210         92164
1357786590  313         92149
1357786590  210         92164
1357786290  313         92149
1357786290  210         92164

I need a result that looks like:

unix_timestamp     tag(92149)     tag(92164)
1357788990         313            210
1357788690         313            210
|
|
Vetc...

Also needing a string "undefined" or a 'NULL' if there is any missing data from one tag if there is any other data for the other tags on a timestamp.

Any help?

Upvotes: 1

Views: 71

Answers (3)

John Woo
John Woo

Reputation: 263733

SELECT  unix_timestamp,
        MAX(CASE WHEN tag = 92149 THEN input_raw ELSE NULL END) [tag92149],
        MAX(CASE WHEN tag = 92164 THEN input_raw ELSE NULL END) [tag92164]
FROM    tableName
GROUP BY unix_timestamp

OR

SELECT unix_timestamp, [92149] As tag92149, [92164] as tag92164
FROM
    (
      SELECT    unix_timestamp, 
                tag,
                input_raw
      FROM      tableName
    ) a
    PIVOT
    (
      MAX(input_raw)
      for tag in ([92149], [92164])
    ) b

Upvotes: 1

Taryn
Taryn

Reputation: 247720

This is a pivot of the data. You did not specify what RDBMS you are using but you can use the following in all databases:

SELECT unix_timestamp,
  max(case when inputs.tag_id = 92149 then input_raw else null end) tag_92149,
  max(case when inputs.tag_id = 92164 then input_raw else null end) tag_92164
FROM [200030].[dbo].inputs
GROUP BY unix_timestamp

See SQL Fiddle with Demo

If you are using a database with a PIVOT function (SQL Server/Oracle) then you can use:

select *
from
(
  select unix_timestamp,
    input_raw,
    tag_id 
  from [200030].[dbo].inputs
) src
pivot
(
  max(input_raw)
  for tag_id in ([92149], [92164])
) piv

See SQL Fiddle with Demo

The result of both is:

| UNIX_TIMESTAMP | 92149 | 92164 |
----------------------------------
|     1357786290 |   313 |   210 |
|     1357786590 |   313 |   210 |
|     1357786890 |   313 |   210 |
|     1357787190 |   313 |   210 |
|     1357787490 |   313 |   210 |
|     1357787790 |   313 |   210 |
|     1357788090 |   313 |   210 |
|     1357788390 |   313 |   210 |
|     1357788690 |   313 |   210 |
|     1357788990 |   313 |   210 |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269933

This is a pivoting query. You can do this in any database using:

select unix_timestamp, 
       max(case when tag = 92149 then input_raw end) as tag_92349,
       max(case when tag = 92164 then input_raw end) as tag_92164
from (SELECT TOP 20 inputs.unix_timestamp, inputs.input_raw, inputs.tag_id 
     FROM [200030].[dbo].inputs
     WHERE inputs.tag_id = 92149 or inputs.tag_id = 92164
    ) t
group by unix_timestamp
ORDER BY unix_timestamp DESC

Upvotes: 1

Related Questions