Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Complex data to join rows from the same table Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 4:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 08, 2014 6:53 AM
Points: 14, Visits: 42
Hi All

I had a huge situations and at the moment only revolve by VB and XML, but is not a fast and easy way to force my users to do.

Real world I receive data from several and diverse equipment and each of one insert on central table the following information.

Date/Time Insert Tag Device Value
------------------- ---------- ----------- ----------
03-28-2013 09:05 1305 125 1251,69
03-28-2013 09:05 1305 126 1261,69
03-28-2013 09:05 1305 127 1271,69
03-28-2013 09:05 1305 128 1281,69
03-28-2013 09:05 1305 129 1291,69
03-28-2013 09:05 1305 130 1301,69
03-28-2013 09:15 1305 125 1252,69
03-28-2013 09:15 1305 126 1262,69
03-28-2013 09:15 1305 127 1272,69
03-28-2013 09:15 1305 128 1282,69
03-28-2013 09:15 1305 129 1292,69
03-28-2013 09:15 1305 130 1302,69

I try several Joing {Inner / All / Out} but not with no sucess., using also a temporary table , but with VB

I need a single output from data above decribe like this:
Data/Time D125 D126 D127 D128 D129 D130
------------------ ---------- ---------- ---------- ---------- ---------- ------------
03-28-2013 09:00 1251,69 1261,69 1271,69 1281,69 1291,69 1301,69
03-28-2013 09:15 1252,69 1262,69 1272,69 1282,69 1292,69 1302,69


I now is not so easy ask, but I thought I can bring this query directly from SQL Server.

Any one can give some ideia to do this.

Thanks

Paulo Afonso
Post #1436859
Posted Friday, March 29, 2013 4:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 12,212, Visits: 9,193
If I'm not mistaken you want a row for every date/time combination and put the values in different columns?
This would be done with a PIVOT statement.

Using PIVOT and UNPIVOT




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1436864
Posted Friday, March 29, 2013 5:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 08, 2014 6:53 AM
Points: 14, Visits: 42
Koen

When we go in one direction, forgot completly other.

Pivot/Unpivot was so obsvious I forgot of this statment.

Thanks for call me for wake up.

For others who need to read examples in easy way, please go to:
[url=http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/][/url]


Paulo
Post #1436883
Posted Friday, March 29, 2013 5:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 08, 2014 6:53 AM
Points: 14, Visits: 42
Koen Verbeeck (3/29/2013)
If I'm not mistaken you want a row for every date/time combination and put the values in different columns?
This would be done with a PIVOT statement.

Using PIVOT and UNPIVOT
Post #1436884
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse