Display columns from multiple rows in single
-
Hello, Im looking for assistance. I have this query and need to display the results in a single row.
select
System, CONVERT(varchar(12), DATEADD(minute, DATEDIFF(minute, [Run Start Time], [Run end Time]), 0),
114) As 'Total hours'
FROM [dbo].[Prod_ShiftSummary]
where office = 'Dallas' and [Run end Time] >= DATEADD(day, -7, GETDATE()) and oven = 6
group by oven,[Run end Time], shift , [Totalweight] ,[Run Start Time],[Run end Time]Currently displays like the left table but I'm looking for it to display like the table on the right.
- This topic was modified 1 year, 5 months ago by gjoelson 29755.
-
Check out this article by Jeff Moden: https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-converting-rows-to-columns-1
-
If you want better help, please post your example data as "Readily Consumable" data instead of a graphic. See the article at the first link in my signature line below for one of a few ways to do such a thing.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Got no idea if this will work as you haven't posted any consumable data:
;WITH CTE AS
(
SELECT System,
T.Hours,
ROW_NUMBER() OVER (PARTITION BY System ORDER BY T.Hours) rn
FROM [dbo].[Prod_ShiftSummary]
CROSS APPLY(VALUES (CONVERT(varchar(12), DATEADD(minute, DATEDIFF(minute, [Run Start Time], [Run end Time]), 0), 114))) T(Hours)
WHERE office = 'Dallas'
AND [Run end Time] >= DATEADD(day, -7, GETDATE())
AND oven = 6
GROUP BY oven, [Run end Time], shift , [Totalweight] ,[Run Start Time], [Run end Time]
)
SELECT System,
MAX(IIF(rn = 1, Hours, NULL)) Hours1,
MAX(IIF(rn = 2, Hours, NULL)) Hours2,
MAX(IIF(rn = 3, Hours, NULL)) Hours3,
MAX(IIF(rn = 4, Hours, NULL)) Hours4,
MAX(IIF(rn = 5, Hours, NULL)) Hours5,
MAX(IIF(rn = 6, Hours, NULL)) Hours6
FROM cte
GROUP BY System
ORDER BY System DESC
; -
Noted ! - Please find my code and data posted below :
CREATE TABLE
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
/****** Object: Table [dbo].[Prod_ShiftSummary] Script Date: 6/5/2023 5:01:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #mytable(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EntryDate] [datetime] NULL,
[Office] [nvarchar](max) NULL,
[Oven] [nvarchar](max) NULL,
[Run] [nvarchar](max) NULL,
[Run Start Time] [datetime] NULL,
[Run end Time] [datetime] NULL,
[shift] [nvarchar](max) NULL,
[Username] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOSELECT 'SELECT '
+ QUOTENAME(ID,'''')+','
+ QUOTENAME([EntryDate],'''')+','
+ QUOTENAME([Office],'''')+','
+ QUOTENAME([Oven],'''')+','
+ QUOTENAME([Run Start Time],'''')
+ QUOTENAME([Run end Time],'''')
-- + ' UNION ALL'
FROM #mytableSAMPLE DATA
insert into #mytable
(Entrydate,Office,Oven,[Run Start Time],[Run end Time])
SELECT '2023-05-25 10:37:00.000','Baytown','1','2023-05-25 10:37:00.000','2023-05-25 11:37:00.000' UNION ALL
SELECT '2023-05-26 10:37:00.000','Baytown','3','2023-05-26 12:21:00.000','2023-05-26 13:12:00.000' UNION ALL
SELECT '2023-05-27 10:37:00.000','Baytown','4','2023-05-26 14:11:00.000','2023-05-26 15:37:00.000' UNION ALL
SELECT '2023-05-28 10:37:00.000','Baytown','6','2023-05-26 15:11:00.000','2023-05-26 15:22:00.000' UNION ALL
SELECT '2023-05-29 10:37:00.000','Baytown','6','2023-05-27 10:33:00.000','2023-05-27 13:44:00.000'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF -
;WITH CTE AS
(
SELECT oven,
T.Hours,
ROW_NUMBER() OVER (PARTITION BY oven ORDER BY T.Hours) rn
FROM #mytable
CROSS APPLY(VALUES (CONVERT(varchar(12), DATEADD(minute, DATEDIFF(minute, [Run Start Time], [Run end Time]), 0), 114))) T(Hours)
)
SELECT oven,
MAX(IIF(rn = 1, Hours, NULL)) Hours1,
MAX(IIF(rn = 2, Hours, NULL)) Hours2,
MAX(IIF(rn = 3, Hours, NULL)) Hours3,
MAX(IIF(rn = 4, Hours, NULL)) Hours4,
MAX(IIF(rn = 5, Hours, NULL)) Hours5,
MAX(IIF(rn = 6, Hours, NULL)) Hours6
FROM cte
GROUP BY oven
ORDER BY oven DESC
;This is probably faster on a lot of data:
;WITH CTE AS
(
SELECT oven,
T.Hours,
ROW_NUMBER() OVER (PARTITION BY oven ORDER BY T.Hours) rn
FROM #mytable
CROSS APPLY(VALUES (CONVERT(varchar(12), DATEADD(minute, DATEDIFF(minute, [Run Start Time], [Run end Time]), 0), 114))) T(Hours)
)
SELECT a.oven,
a.Hours Hours1,
b.Hours Hours2,
c.Hours Hours3,
d.Hours Hours4,
e.Hours Hours5,
f.Hours Hours6
FROM cte a
LEFT JOIN cte b ON b.rn = 2 AND b.oven = a.oven
LEFT JOIN cte c ON c.rn = 3 AND c.oven = a.oven
LEFT JOIN cte d ON d.rn = 4 AND d.oven = a.oven
LEFT JOIN cte e ON e.rn = 5 AND e.oven = a.oven
LEFT JOIN cte f ON f.rn = 6 AND f.oven = a.oven
WHERE a.rn = 1
ORDER BY a.oven DESC
; -
Looking at the first picture above the ordering of the Hours doesn't seem clear. Is it ASCENDING by [Run Start Time]? In the output, when the System value equals 6, reading across, it's down, down, up. Where the System value equals 5 it's down, up. Also, what's going on with QUOTENAME here? In the DDL the column names contain spaces (100% I recommend to never do this because it's a pita which lasts forever) and are correctly delimited with brackets. Everywhere else, in perpetuity, the names referenced in SQL should be similarly delimited with brackets. Afaik brackets are the only official delimiter. In tools like SSMS and Visual Studio these things (ticks and brackets) might work interchangeably. When it comes to data access though 3rd party tools sometimes don't and the error messages could be cryptic. Imo a choice between underscore and mixed case naming is necessary. For my part I prefer underscores
Ha, AI agrees with what I'm saying
USER
In SQL Server true or false, the only officially official object naming delimiter to encompass spaces is brackets? Sometimes tick marks work also but it's asking for trouble imo
ASSISTANT
True. In SQL Server, the officially recommended object naming delimiter to encompass spaces is brackets [ ]. Although tick marks or backticks (`) might work in some situations, they are not officially recommended and can cause issues. It's best to stick with brackets [ ] for delimiting object names with spaces in SQL Server.Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Is the QUOTENAME to make the sample data? 🙂
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- Steve Collins wrote:
Is the QUOTENAME to make the sample data? 🙂
Yes... the QUOTENAME is supposed to be used just to make the sample data and isn't required to be posted. It looks like I may have to update the article to make that more clear.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok thanks. Yeah maybe I misread this one. When there are spaces in object labels it causes flashbacks to a difficult job
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply