February 23, 2013 at 3:06 am
Dear All
I have a query select Id from abc which returs
1
2
3
I want it to return
1
go
2
go
3
go
Is there a way to achive this suing single selct or i need to use cursor only?
February 23, 2013 at 3:23 am
I'd use the cte approach. I'm confident it can be compressed a little further but this would be my starting point:
WITH cte_originalquery AS
(
SELECT 1 AS Number UNION ALL
SELECT 2 AS Number UNION ALL
SELECT 3 AS Number
),
cte_originalqueryExpanded AS -- add two columns to be used for output order
(
SELECT
1 AS Sort,
Number AS Sort2,
Number
FROM cte_originalquery
),
cte_helperquery AS-- add the required number of "go" lines using separate cte
(
SELECT
2 AS Sort,
Number AS Sort2,
'GO' AS AddLine
FROM cte_originalquery
),
cte_merged AS-- merge the two queries
(
SELECT
Sort,
Sort2,
AddLine
FROM cte_helperquery
UNION ALL
SELECT
Sort,
Sort2,
CAST(Number AS VARCHAR(10))
FROM cte_originalqueryExpanded
)
-- final output, ordered properly
SELECT AddLine as final
FROM cte_merged
ORDER BY Sort2,Sort
February 23, 2013 at 3:58 am
thanks it worked
February 27, 2013 at 4:11 am
I'm assuming you want to copy the output and execute it.
Try this
select col1 + char(10) + 'go' from table
https://sqlroadie.com/
February 27, 2013 at 4:22 am
LutzM (2/23/2013)
I'd use the cte approach. I'm confident it can be compressed a little further but this would be my starting point:
WITH cte_originalquery AS(
SELECT 1 AS Number UNION ALL
SELECT 2 AS Number UNION ALL
SELECT 3 AS Number
),
cte_originalqueryExpanded AS -- add two columns to be used for output order
(
SELECT
1 AS Sort,
Number AS Sort2,
Number
FROM cte_originalquery
),
cte_helperquery AS-- add the required number of "go" lines using separate cte
(
SELECT
2 AS Sort,
Number AS Sort2,
'GO' AS AddLine
FROM cte_originalquery
),
cte_merged AS-- merge the two queries
(
SELECT
Sort,
Sort2,
AddLine
FROM cte_helperquery
UNION ALL
SELECT
Sort,
Sort2,
CAST(Number AS VARCHAR(10))
FROM cte_originalqueryExpanded
)
-- final output, ordered properly
SELECT AddLine as final
FROM cte_merged
ORDER BY Sort2,Sort
Is all that necessary Lutz? Could you not do something like this: -
--Sample data
IF object_id('tempdb..#abc') IS NOT NULL
BEGIN
DROP TABLE #abc;
END;
SELECT Id
INTO #abc
FROM (VALUES(1),(2),(3))a(Id);
--==========================================================================--
--ACTUAL QUERY
--==========================================================================--
SELECT Id
FROM (SELECT CAST(Id AS VARCHAR(2)), (ROW_NUMBER() OVER(ORDER BY Id)*2)-1
FROM #abc
UNION ALL
SELECT 'go', ROW_NUMBER() OVER(ORDER BY Id)*2
FROM #abc
)a(Id,Sort)
ORDER BY Sort;
SET NOCOUNT ON;
--Sample data
IF object_id('tempdb..#abc') IS NOT NULL
BEGIN
DROP TABLE #abc;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS Id
INTO #abc
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
DECLARE @HOLDER VARCHAR(10), @Duration CHAR(12), @StartTime DATETIME;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER = Id
FROM (SELECT CAST(Id AS VARCHAR(10)), (ROW_NUMBER() OVER(ORDER BY Id)*2)-1
FROM #abc
UNION ALL
SELECT 'go', ROW_NUMBER() OVER(ORDER BY Id)*2
FROM #abc
)a(Id,Sort)
ORDER BY Sort;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Duration: %s',0,1,@Duration) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
with cte_originalqueryExpanded AS -- add two columns to be used for output order
(
SELECT
1 AS Sort,
Id AS Sort2,
Id
FROM #abc
),
cte_helperquery AS-- add the required number of "go" lines using separate cte
(
SELECT
2 AS Sort,
Id AS Sort2,
'GO' AS AddLine
FROM #abc
),
cte_merged AS-- merge the two queries
(
SELECT
Sort,
Sort2,
AddLine
FROM cte_helperquery
UNION ALL
SELECT
Sort,
Sort2,
CAST(Id AS VARCHAR(10))
FROM cte_originalqueryExpanded
)
-- final output, ordered properly
SELECT @HOLDER = AddLine
FROM cte_merged
ORDER BY Sort2,Sort
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Duration: %s',0,1,@Duration) WITH NOWAIT;
Over a million rows (see above), the results were: -
Duration: 00:00:04:043
Duration: 00:00:03:893
So Lutz' was about 0.150 seconds faster.
SELECT Id
FROM (SELECT CAST(Id AS VARCHAR(10)), (Id*2)-1
FROM #abc
UNION ALL
SELECT 'go', Id*2
FROM #abc
)a(Id,Sort)
ORDER BY Sort;
Now stick that into the 1 million row test harness: -
SET NOCOUNT ON;
--Sample data
IF object_id('tempdb..#abc') IS NOT NULL
BEGIN
DROP TABLE #abc;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS Id
INTO #abc
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
DECLARE @HOLDER VARCHAR(10), @Duration CHAR(12), @StartTime DATETIME;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER = Id
FROM (SELECT CAST(Id AS VARCHAR(10)), (Id*2)-1
FROM #abc
UNION ALL
SELECT 'go', Id*2
FROM #abc
)a(Id,Sort)
ORDER BY Sort;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Duration: %s',0,1,@Duration) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
with cte_originalqueryExpanded AS -- add two columns to be used for output order
(
SELECT
1 AS Sort,
Id AS Sort2,
Id
FROM #abc
),
cte_helperquery AS-- add the required number of "go" lines using separate cte
(
SELECT
2 AS Sort,
Id AS Sort2,
'GO' AS AddLine
FROM #abc
),
cte_merged AS-- merge the two queries
(
SELECT
Sort,
Sort2,
AddLine
FROM cte_helperquery
UNION ALL
SELECT
Sort,
Sort2,
CAST(Id AS VARCHAR(10))
FROM cte_originalqueryExpanded
)
-- final output, ordered properly
SELECT @HOLDER = AddLine
FROM cte_merged
ORDER BY Sort2,Sort
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Duration: %s',0,1,@Duration) WITH NOWAIT;
Duration: 00:00:03:533
Duration: 00:00:03:843
Now mine is fastest by ~310ms 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply