New answers tagged

4

This is a classic case of a pivot operation in SQL. The method working in, probably, most (if not all) SQL products, including SQL Server, is conditional aggregation: SELECT UserID, Year, M1 = SUM(CASE Mth WHEN 1 THEN Value END), M2 = SUM(CASE Mth WHEN 2 THEN Value END), M3 = SUM(CASE Mth WHEN 3 THEN Value END), M4 = SUM(CASE Mth WHEN 4 ...


0

Since the target table obviously exists, CREATE TABLE AS is unrelated to the solution. And you certainly don't need a recursive query, either. For just three columns you might use conditional aggregates: INSERT INTO reports_con (id_station, date, tmin, tmax, tavg) SELECT id_station, date , min(value) FILTER (WHERE element = 'TMIN') AS tmin , min(...


1

Here you go. This is for SQL Server (since you haven't tagged the DB) but I guess this is OK since you mention SCCM. There are heaps of examples of dynamic pivots on here if the final number of [UserX] columns is unknown. SELECT * INTO #Temp FROM (VALUES ('PC001', 'a1\user1') ,('PC002', 'a1\user2') ,('PC002', 'a1\user3') ,('PC003', 'a1\user4') ,('PC003', ...


0

The code is using a "Common Table Expressions" or "CTE". You can use temporary tables instead: select Code,Location INTO #data from ( values ('L698-W-EA', NULL) ,('L82009-EA', '1/22/333/4444/55555') ,('L80401-A-EA', '7/88/999/10-10/12') ,('CWD2132W-BOX-25PK', 'A-AISLE') ,('GM22660003-EA', '...


1

Use a Matrix control in SSRS. But in T-SQL: You want a row per ID so you'll need GROUP BY ID In the first column you want a time value where the activity is A, so use: MAX(CASE WHEN Activity = 'Activity A' THEN time END) And then apply the pattern for all the others.



Top 50 recent answers are included