Tag Info

New answers tagged

0

From your question you're looking a billion sensor readings, plus or minus. Using an int for the foreign key to the parameters, and for the data itself, and another to separate each sensor reading from the others in a single file, that works out at just under 12GB of sensor data. All up the DB's going to be quite manageable by modern standards. You don't ...


0

To be able to PIVOT values into the shape of your target Parameter table you need a set of (parameter_name, parameter_value) tuples. I assume the design is able to distinguish one parameter value from another, either because of its position in the file (row 1 is Parameter A etc.) or due to a label in the row (ParamA=ValueA etc.). For a positional design, ...


0

using ssis and some c# / vba add a foreach loop to iterate over the files create a variable to use for the key (increment on each iteration) in the loop add a dataflow consisting of a script transform (source) to read first 40 lines from the file and pivot a derived column to add the key (from var) a destination to the table in the loop add a dataflow ...


0

The way you are describing the expected result; I am concerned that what you are looking for won't get you what you want. Taking the example from the referenced post, you may actually want: create view yourview1 as ( select user_id, case when Item_Type = "first_name" then meta_value end as first_name, case when Item_Type = "last_name" then ...


0

You could do something along the following lines: select s1.grade, s1.firstname as s1_fname, s2.firstname as s2_fname, s3.firstname as s3_fname, s4.firstname as s4_fname from #grades as s1 -- Self join the table for second student left outer join grades as s2 on s1.grade = s2.grade and s2.student_num = 2 -- Self join the ...


0

You can try this kind of "solution": http://sqlfiddle.com/#!9/d33a95/18 It is not an "pivot-table" solution, but maybe it solves your problem - if I have understood it correctly. Here are the tables / data used: CREATE TABLE `patients` ( `ID` INT NOT NULL AUTO_INCREMENT , `Name` VARCHAR( 50 ) NOT NULL , PRIMARY KEY ( `ID` ) ); CREATE TABLE `controls` ( ...


0

Please post question correctly. I am not getting your schema & your second query is showing @sql variable value to same because of last record value in first query stored to @sql due to last write. Post schema of table with some dummy data. I am trying to understand & conclude that you need column sub-query. I am setting example which could help ...


4

Adding explanation and a fiddle: http://sqlfiddle.com/#!6/c92b2/5. The query bellow: 1. uses a subquery to select the min and max versions by date (min and max are applied to integers to guarantee that for instance 6 < 16) 2. Then Selects the year (to group later), Date (to order) and the min - max versions SELECT LEFT(UPG_DATE, 4) AS Year , ...


18

This is a bit messy to get the final result because you have multiple SCHEMA_VER for each date. Before I demonstrate how to do this with dynamic SQL, I'll first show how to do it with static code to get the logic correct. In order to get the final result you can utilize both pivot and unpivot. But first, I'd change your original query to use the following: ...


1

So here's what I did. I put what you had in a temp table and then grouped the results like so: ;WITH data AS ( SELECT grp, k, k_ts = k + '_ts', ts, val FROM @Source ) SELECT * INTO #tmp FROM data PIVOT ( MAX(val) FOR k IN ([color], [shape], [subst]) ) p1 PIVOT ( MAX(ts) FOR k_ts IN ([color_ts], [shape_ts], [subst_ts]) ) p2 Select grp , ...


2

If you're okay with a single column showing the IP addresses as a comma-separated list: SELECT SYS.Netbios_Name0 AS [Computer Name], SYS.User_Name0 AS [Last Login User], SYS.User_Domain0 AS [User Domain], Sys.Resource_Domain_OR_Workgr0 AS [System Domain], v_GS_Computer_System. Manufacturer0 AS [Manufacturer Name], ...



Top 50 recent answers are included