June 23, 2015 at 11:06 pm
Hello All,
I Have Problem Sql Query for Get Data for New Table
Iam New In SQL Server.
I Have SQL 2008 Table [Report]
Tagname|Time_1|Data_1|Time_2|Data_2|
Test1|07.00|20|07.30|40|
Test2|07.00|30|07.30|50|
Then I Want Get Data And Make New Table From That Table.
Like This:
Time|Test1|Test2
07.00|20|30
07.30|40|50
Please Help Me To Solve The Query.
Thanks All
txtPost_CommentEmoticon(':-)');
June 24, 2015 at 1:50 am
Below makes use of a "Cross Tab" which if you search for on this site you should find some articles detailing it.
--Create Test Data
WITH BaseData (TagName,Time_1,Data_1,Time_2,Data_2)
AS
(
SELECTA.TagName,
A.Time_1,
A.Data_1,
A.Time_2,
A.Data_2
FROM(
VALUES('Test1',CAST('07:00' AS TIME),20,CAST('07:30' AS TIME),40),
('Test2',CAST('07:00' AS TIME),30,CAST('07:30' AS TIME),50)
) AS A(TagName,Time_1,Data_1,Time_2,Data_2)
)
--CrossTab Query
SELECTCA1.TimeCol,
Test1 = MAX(CASE WHEN BD.TagName = 'Test1' THEN CA1.Data END),
Test2 = MAX(CASE WHEN BD.TagName = 'Test2' THEN CA1.Data END)
FROMBaseData AS BD
--Use Cross Apply to unpivot data
CROSS
APPLY(SELECT A.TimeCol,A.Data FROM (VALUES (Time_1,Data_1),(Time_2,Data_2)) AS A(TimeCol,Data)) AS CA1
GROUPBY CA1.TimeCol;
Please note if you have more than Test1/Test2 you will need to add these in as well
June 24, 2015 at 3:09 am
Thanks Mr Dohsan For Answer,
I Have Trend Record Data Using SQL Server 2012, Data Collected By Other Program Use SQL Server.
I Confuse Because Data Format Devided By Time Grouping,
Example
Tagname|Time_1|Data_1|Time_2|Data_2| ---> Up To 36 Group |Time_36|Data_36|
Test1|07.00|20|07.30|40| |00.30| 15|
Test2|07.00|30|07.30|50| |00.30| 56|
I Want Use That Data In Table To Make A Report Like This
Time|Test1|Test2
07.00|20|30
07.30|40|50
|
|
00.30|15|56
The Report Recall By Operator Using Other Program
By Adjust Date
June 24, 2015 at 3:20 am
ags.saputra88 (6/24/2015)
Thanks Mr Dohsan For Answer,I Have Trend Record Data Using SQL Server 2012, Data Collected By Other Program Use SQL Server.
I Confuse Because Data Format Devided By Time Grouping,
Example
Tagname|Time_1|Data_1|Time_2|Data_2| ---> Up To 36 Group |Time_36|Data_36|
Test1|07.00|20|07.30|40| |00.30| 15|
Test2|07.00|30|07.30|50| |00.30| 56|
I Want Use That Data In Table To Make A Report Like This
Time|Test1|Test2
07.00|20|30
07.30|40|50
|
|
00.30|15|56
The Report Recall By Operator Using Other Program
By Adjust Date
That should be fine to do as long as you add the additional columns that you wish to unpivot. I've added some more below as an example, you can add the rest yourself.
--Create Test Data
WITH BaseData (TagName,Time_1,Data_1,Time_2,Data_2,Time_3,Data_3,Time_4,Data_4,Time_5,Data_5)
AS
(
SELECTA.TagName,
A.Time_1,
A.Data_1,
A.Time_2,
A.Data_2,
A.Time_3,
A.Data_3,
A.Time_4,
A.Data_4,
A.Time_5,
A.Data_5
FROM(
VALUES('Test1',CAST('07:00' AS TIME),20,CAST('07:30' AS TIME),40,CAST('08:00' AS TIME),40,CAST('08:30' AS TIME),40,CAST('09:00' AS TIME),40),
('Test2',CAST('07:00' AS TIME),30,CAST('07:30' AS TIME),50,CAST('08:00' AS TIME),40,CAST('08:30' AS TIME),40,CAST('09:00' AS TIME),40)
) AS A(TagName,Time_1,Data_1,Time_2,Data_2,Time_3,Data_3,Time_4,Data_4,Time_5,Data_5)
)
--CrossTab Query
SELECTCA1.TimeCol,
Test1 = MAX(CASE WHEN BD.TagName = 'Test1' THEN CA1.Data END),
Test2 = MAX(CASE WHEN BD.TagName = 'Test2' THEN CA1.Data END)
FROMBaseData AS BD
--Use Cross Apply to unpivot data
CROSS
APPLY(SELECT A.TimeCol,A.Data FROM (
VALUES(Time_1,Data_1)
,(Time_2,Data_2)
--add additional column pairs here
-- up to 36 etc.
,(Time_3,Data_3)
,(Time_4,Data_4)
,(Time_5,Data_5)
) AS A(TimeCol,Data)) AS CA1
GROUPBY CA1.TimeCol;
My suggestion would be to search for "pivot cross apply" and "cross tab queries" on this site and also have a play around with the query so you understand how it works.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply