SQL Server View one column as two

Question

Table looks like:

For the same RDATE and TAGNAME like 'WOJ11_%' should:

For any helps thank you very much.

CREATE TABLE [dbo].[RD](
    [RDATE] [datetime] NULL,
    [TAGNAME] [nvarchar](50) NULL,
    [RVALUE] [float] NULL,
    [RMINVALUE] [float] NULL,
    [RMAXVALUE] [float] NULL
) ON [PRIMARY]

INSERT INTO [dbo].[RD]
VALUES
('2017-04-04 18:00:00.000', 'WOJ11_Q1', 0, 0, 0),
('2017-04-04 18:00:00.000', 'WOJ11_P2', 0, 0, 0),
('2017-04-04 18:00:00.000', 'WOJ11_P1', 0, 0, 0),
('2017-04-04 19:00:00.000', 'WOJ11_Q1', 65.12, 61.52, 68.52),
('2017-04-04 19:00:00.000', 'WOJ11_P2', 5.82833333333333, 5.796, 5.874),
('2017-04-04 19:00:00.000', 'WOJ11_P1', 6.18733333333333, 6.158, 6.227),
('2017-04-04 20:00:00.000', 'WOJ11_Q1', 75.72, 72.68, 77.44),
('2017-04-04 20:00:00.000', 'WOJ11_P2', 5.7345, 5.698, 5.776),
('2017-04-04 20:00:00.000', 'WOJ11_P1', 6.07925, 6.04, 6.128),
('2017-04-04 21:00:00.000', 'WOJ11_Q1', 72.2, 68.32, 79.56),
('2017-04-04 21:00:00.000', 'WOJ11_P2', 5.80575, 5.688, 5.884),
('2017-04-04 21:00:00.000', 'WOJ11_P1', 6.109, 6.021, 6.168),
('2017-04-04 22:00:00.000', 'WOJ11_Q1', 66.48, 59.68, 70.72),
('2017-04-04 22:00:00.000', 'WOJ11_P2', 5.86675, 5.766, 5.972),
('2017-04-04 22:00:00.000', 'WOJ11_P1', 6.175, 6.119, 6.236);

output table for 'WOJ11_%' (3 tags) should look like:

CREATE TABLE [dbo].[RD_OUTPUT](
    [RDATE] [datetime] NULL,
    [TAGNAME_P1_RVALUE] [float] NULL,
    [TAGNAME_P1_RMINVALUE] [float] NULL,
    [TAGNAME_P1_RMAXVALUE] [float] NULL,
    [TAGNAME_P2_RVALUE] [float] NULL,
    [TAGNAME_P2_RMINVALUE] [float] NULL,
    [TAGNAME_P2_RMAXVALUE] [float] NULL,
    [TAGNAME_Q1_RVALUE] [float] NULL,
    [TAGNAME_Q1_RMINVALUE] [float] NULL,
    [TAGNAME_Q1_RMAXVALUE] [float] NULL
) ON [PRIMARY]

with values:

INSERT INTO [dbo].[RD_OUTPUT]
VALUES
('2017-04-04 18:00:00.000', 0, 0, 0, 0, 0, 0, 0, 0, 0),
('2017-04-04 19:00:00.000', 6.18733333333333, 6.158, 6.227, 5.82833333333333, 5.796, 5.874, 65.12, 61.52, 68.52),
('2017-04-04 20:00:00.000', 6.07925, 6.04, 6.128, 5.7345, 5.698, 5.776, 75.72, 72.68, 77.44),
('2017-04-04 21:00:00.000', 6.109, 6.021, 6.168, 5.80575, 5.688, 5.884, 72.2, 68.32, 79.56),
('2017-04-04 22:00:00.000', 6.175, 6.119, 6.236, 5.86675, 5.766, 5.972, 66.48, 59.68, 70.72);

Your Question is Not clear Add structure of your table with sample data and expected output — , May 16 '17 at 13:07
Please post data as text, not as (link to) image. You are looking for `pivot` — HoneyBadger, May 16 '17 at 13:09
Reasons not to use images are [here](http://meta.stackoverflow.com/a/285557/92546). — HABO, May 16 '17 at 13:48

score 0 · Accepted Answer · answered May 16 '17 at 13:37

0

You can use simple pivot

select * from (
select * from #yourtable where Tagname like 'WOJ11_%' ) a
pivot (max(RValue) for TagName in ([WOJ11_P1],[WOJ11_P2],[WOJ11_Q1])) p

If multiple tagnames you might require to do dynamic query to generate columns and provide it in "IN" clause

answered May 16 '17 at 13:37

Kannan Kandasamy

13,405
3
25
38

Thanks a lot. It work but now I've more difficult scenario. I've added two columns in main table (RMINVALUE, RMAXVALUE). Finaly I need 9 columns - three per one tag (WOJ11_P1, WOJ11_P1MIN, WOJ11_P1MAX etc.) I know that is duplicated but you know... missing skills. – UNGBAR May 29 '17 at 14:33
If you provide input table structure and expected table structure in scripts/texts it will be more helpful – Kannan Kandasamy May 29 '17 at 14:35
Source and output/expected table updated in main post. Please take a look. – UNGBAR May 30 '17 at 06:24

SQL Server View one column as two

1 Answers1