Tag Info

New answers tagged

2

Is it possible to redesign your schema? It feels like you are making life harder for yourself by basically trying to pivot the data you're importing from the excel spreadsheets. CREATE TABLE dbo.Hardware -- hw? ( [Event_Num] INT NOT NULL PRIMARY KEY, [Name] NVARCHAR(100) NOT NULL, [Install_Date] DATE NULL, -- Install after pulling? ...


0

I would go for a 2 step solution. I use simple SQL. You can use the right names and fields. First I would update the existing rows: update tab1 set tab1.field = tab1.field + tmp1.field from table1 tab1 inner join temp1 tmp1 on tab1.key=tmp1.key; Secondly I would insert all non-existing rows: insert into table1 (key, field) select key, field from temp1 ...


0

I agree a pivot should get you what you need, but you should be aware that the column names are going to be fixed. Something like the following should work. You could also extend this example to use dynamic column names if you use dynamic SQL. SELECT * FROM ( SELECT [from] AS [Sender] , DATENAME(DW, [date]) + N'' + ...


1

You could use dynamic SQL to loop through the years. DECLARE @SQL NVARCHAR(MAX) SET @SQL = 'SELECT '; DECLARE @intYear INT SET @intYear = 2013 WHILE (@intYear < Year(GETDATE())) BEGIN SET @SQL = @SQL + '(SELECT SUM(EmployeePaid)*2 AS CurrYear FROM TblRecords WHERE FYear = ' + CAST(@intYear as CHAR(4)) + ') as Total' + CAST(@intYear as CHAR(4)) + ...


4

Iterative approaches do have their place, but generally speaking it is much better to use set based approaches when working with databases. I've heard that the math says that "set based solutions will be faster than iterative solutions in the vast majority of the cases" several times, but I can't seem to find a good reference at the moment. If you really ...


2

I was wondering whether you want to group by well_name for all different pull date or not. Right now, a well with X dates gets X rows. If you want the X date/rows to become 1, I will update the queries. Query 1 uses left join Query 2 uses pivot SQL Fiddle (Left Join): ;with data as( Select Pull_Date, Well_Name, Part, PartPN, Part_SN , id = ...


2

This should work. I took the original CTE, partitioned by part and date and numbered those. This gave every part on each date it's own number starting with 1. Then in the case statement, adding WHERE Number = N allows it so that each part is unique. Finally, the column names were modified to have their respective numbers. Since this is unwieldy, ...


0

@PaulBarbin's answer is great if you know that you will always need to output three columns of data. If you need to dynamically output a different number of columns based on the data being selected then you will have to adapt his solution using dynamic sql. Effectively you are trying to get SqlServer to output data contrary to what its natural inclination ...


3

You could try Pivot: I think you'll need to order on some key that ties these records together (how do you know that the starttime of "TESR" is related to the EndTime of "TESR". That probably needs to be in the ORDER BY clause below. SELECT Title ,[1] ,[2] ,[3] FROM ( SELECT Title, Value, ...


0

The pivot of this would look something like the code example below. @Lennart's answer is much simpler as you can easily see. USE [tempdb]; GO IF OBJECT_ID('test' , 'U') IS NOT NULL BEGIN DROP TABLE [dbo].[test] END; CREATE TABLE [dbo].[test] ( [ID] INT IDENTITY NOT NULL , [Name] CHAR(5) NOT NULL , [Result] CHAR(4) NOT NULL , [Date] ...


3

A PIVOT that will work in most DBMS is: select name , count(case when Result = 'Win' and Month(dt) = 1 then 1 end) as Jan , count(case when Result = 'Win' and Month(dt) = 2 then 1 end) as Feb , count(case when Result = 'Win' and Month(dt) = 3 then 1 end) as Mar from t group by name I used dt as name for your date column since date is a ...



Top 50 recent answers are included