Dynamic Statement with Cursor loop, help
-
Hi ,
I am trying to build a dynamic query which will create a select. I am trying to use a cursor, cause I don't know in advance how many fields I will get from the cursor loop.
I am currently trying this approach, which is no
Can someone help out?
DECLARE @sqlCommand nvarchar(4000)
DECLARE @sqlCommand1 nvarchar(4000)
DECLARE @sqlCommand2 nvarchar(4000)
DECLARE @sqlCommand3 nvarchar(4000)SET @sqlCommand1 = 'SELECT cus.CompanyName'
DECLARE @SalesItemId nvarchar(max)
DECLARE s_cursor CURSOR FOR
SELECT distinct [SalesItemID] FROM KeyFigures_SalesItemsOPEN s_cursor
-- Perform the first fetch.
FETCH NEXT FROM s_cursor
INTO @SalesItemId-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGINSET @sqlCommand2 = @sqlCommand2 + ',CAST( CAST( CONVERT(decimal(18,1),((SELECT SUM([Solgt i perioden KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=' + @SalesItemId + ')+(SELECT SUM([Ultimo lager KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)= cus.dataname and SalesItemId=' + @SalesItemId + '))-((SELECT SUM([Primo lager KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=' + @SalesItemId + ')+(SELECT SUM([Købt i perioden KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=' + @SalesItemId + ')))/ CONVERT(decimal(18,2), (SELECT SUM([Solgt i perioden KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=' + @SalesItemId + ')) as decimal(18,2)) as nvarchar(50)) as procent' + @SalesItemId
FETCH NEXT FROM s_cursor
INTO @SalesItemIdEND
CLOSE s_cursor
DEALLOCATE s_cursorSET @sqlCommand3 = ' FROM [dbo].[CustomerSetup] cus where Not cus.HAVI_ID = 0'
SET @sqlCommand = concat(@sqlCommand1,@sqlCommand2,@sqlCommand3)
EXEC (@sqlCommand)
- peter larsen-490879 - Wednesday, May 17, 2017 7:24 AM
ET @sqlCommand2 = @sqlCommand2 + ',CAST( CAST( CONVERT(decimal(18,1),(
(SELECT SUM([Solgt i perioden KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=' + @SalesItemId + ')+
(SELECT SUM([Ultimo lager KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)= cus.dataname and SalesItemId=' + @SalesItemId + '))-
(
(SELECT SUM([Primo lager KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=' + @SalesItemId + ')+
(SELECT SUM([Købt i perioden KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=' + @SalesItemId + ')))/ CONVERT(decimal(18,2),
(SELECT SUM([Solgt i perioden KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=' + @SalesItemId + ')) as decimal(18,2)) as nvarchar(50)) as procent' + @SalesItemId
This isn't how to write a query. What exactly are you trying to do here?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Hi
I am trying to build records like shown on the image. There can be more Columns like procent1 and procent2
I have the company names in one table
SELECT cus.CompanyName FROM [dbo].[CustomerSetup] cus where Not cus.HAVI_ID = 0 Then i want something like this:
SELECT cus.CompanyName, CAST( CAST( CONVERT(decimal(18,1),((SELECT SUM([Solgt i perioden KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=1)+(SELECT SUM([Ultimo lager KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)= cus.dataname and SalesItemId=1))-((SELECT SUM([Primo lager KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=1)+(SELECT SUM([Købt i perioden KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=1)))/ CONVERT(decimal(18,2), (SELECT SUM([Solgt i perioden KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=1)) as decimal(18,2)) as nvarchar(50)) +'%' as procent1
,CAST( CAST( CONVERT(decimal(18,1),((SELECT SUM([Solgt i perioden KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=2)+(SELECT SUM([Ultimo lager KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)= cus.dataname and SalesItemId=2))-((SELECT SUM([Primo lager KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=2)+(SELECT SUM([Købt i perioden KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=2)))/ CONVERT(decimal(18,2), (SELECT SUM([Solgt i perioden KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=2)) as decimal(18,2)) as nvarchar(50)) +'%' as procent2
,CAST( CAST( CONVERT(decimal(18,1),((SELECT SUM([Solgt i perioden KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=8)+(SELECT SUM([Ultimo lager KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)= cus.dataname and SalesItemId=8))-((SELECT SUM([Primo lager KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=8)+(SELECT SUM([Købt i perioden KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=8)))/ CONVERT(decimal(18,2), (SELECT SUM([Solgt i perioden KG]) FROM [dbo].[KeyFigures_Calculation] where trim(dataname)=cus.dataname and SalesItemId=8)) as decimal(18,2)) as nvarchar(50)) +'%' as procent3
FROM [dbo].[CustomerSetup] cus
where Not cus.HAVI_ID = 0The trouble for me is that i don't know how to determine how many 'procent' columns to create.
Somehow this info could be achieved doing a query against this table.
SELECT distinct [SalesItemID] FROM [dbo].[KeyFigures_SalesItems] 
This distinct SalesItemID is the one i have manually added above in the select for each procent column.
-
You're writing a "dynamic cross-tab query". It's well-documented and very well studied.
To begin with, can you write the query you would need for just the first percent column, please? Write it as simply as you can. This is for two reasons.
Firstly, to get a better idea of how your data fits together. Secondly, to gauge what level of help you will need with this.“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Something like this:
SELECT cus.CompanyName, kc.SalesItemId,
SUM([Solgt i perioden KG]),
SUM([Ultimo lager KG]),
SUM([Primo lager KG]),
SUM([Købt i perioden KG]),
SUM([Solgt i perioden KG])FROM [dbo].[CustomerSetup] cus
LEFT JOIN [dbo].[KeyFigures_Calculation] kc
ON trim(kc.dataname) = cus.dataname -- "trim" isn't TSQL
WHERE cus.HAVI_ID <> 0
GROUP BY cus.CompanyName, kc.SalesItemId“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Why are you using CompanyName in your column list and dataname to relate between the tables?
Here's an example on how to remove the loop and query in an efficient way.
Everything is explained in these articles:
http://www.sqlservercentral.com/articles/Crosstab/65048/
http://www.sqlservercentral.com/articles/comma+separated+list/71700/Here's the untested example:
DECLARE @sqlCommand nvarchar(4000)
DECLARE @sqlCommand1 nvarchar(4000)
DECLARE @sqlCommand2 nvarchar(4000) = ''
DECLARE @sqlCommand3 nvarchar(4000)SELECT @sqlCommand1 = '
WITH CTE AS(
SELECT dataname, SalesItemId,
ISNULL( SUM([Solgt i perioden KG]), 0) AS [Solgt i perioden KG],
ISNULL( SUM([Ultimo lager KG]), 0) AS [Solgt i perioden KG],
ISNULL( SUM([Primo lager KG]), 0) AS [Solgt i perioden KG],
ISNULL( SUM([Købt i perioden KG]), 0) AS [Solgt i perioden KG]
FROM [dbo].[KeyFigures_Calculation]
GROUP BY dataname, SalesItemId
)
SELECT cus.CompanyName' + CHAR(10);SELECT @sqlCommand2 = ( SELECT DISTINCT CHAR(9) + REPLACE( ',SUM( CASE WHEN SalesItemId=<<SalesItemID>> THEN ([Solgt i perioden KG] + [Ultimo lager KG] - ([Primo lager KG] + [Købt i perioden KG]))'
+ ' / [Solgt i perioden KG] END) as procent<<SalesItemID>>', '<<SalesItemID>>', n) + CHAR(10)
FROM KeyFigures_SalesItems
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)');SELECT @sqlCommand3 = 'FROM [dbo].[CustomerSetup] cus
LEFT JOIN CTE ON cus.dataname = CTE.dataname
where Not cus.HAVI_ID = 0
GROUP BY cus.CompanyName';SET @sqlCommand = concat(@sqlCommand1,@sqlCommand2,@sqlCommand3);
PRINT @sqlCommand;
--EXEC (@sqlCommand) -
Hi Chris,
I believethat a simple query with just one column, would be something like
SELECT cus.CompanyName, kc.SalesItemId, N.ItemName AS HeaderName,
SUM([Solgt i perioden KG]) as [Solgt i perioden KG],
SUM([Ultimo lager KG]) as [Ultimo lager KG],
SUM([Primo lager KG]) as [Primo lager KG],
SUM([Købt i perioden KG]) as [Købt i perioden KG]
--,CAST( (CONVERT(decimal(18,1),([Solgt i perioden KG]+[Ultimo lager KG]))-CONVERT(decimal(18,1),([Primo lager KG])+([Købt i perioden KG]))) / (CONVERT(decimal(18,1),[Solgt i perioden KG])) as decimal(18,2)) AS Procent
FROM [dbo].[CustomerSetup] cus
LEFT JOIN [dbo].[KeyFigures_Calculation] kc
ON trim(kc.dataname) = cus.dataname -- "trim" isn't TSQL
INNER JOIN KeyFigures_SalesItemNames N On N.id = kc.SalesItemId
WHERE cus.HAVI_ID <> 0 and not kc.SalesItemId is null
GROUP BY cus.CompanyName, kc.SalesItemId,[Solgt i perioden KG],[Ultimo lager KG],[Primo lager KG],[Købt i perioden KG],ItemName
-
Hi again,
this is the ouput i am trying to achive.
- peter larsen-490879 - Wednesday, May 17, 2017 2:18 PM
You gave the output, but didn't gave the input. How are people supposed to get to that result? Read the links in my signature to understand how to post to get better answers.
You could also check what I posted. -
Hi Luiz
Thank you for answer.
I will provide a better question with sample data.Would you plz. remove the image in your last post. It might not be good to shwo it afterall.
-
Hi all,
I rewrote the post using the suggested tools.
se here: https://www.sqlservercentral.com/Forums/1877075/Reverse-ouput?Update=1#bm1877089 -
-- I find it helps to preaggregate the table down BEFORE the pivot.
-- it's a visual thing - it's easier to see what's going to happen.
-- Dropping the results into a #temp table allows you to
-- make many small changes to your code without having to rerun
-- a potentially expensive main query against base tables which may be in use by others.
IF OBJECT_ID('TempDB..#Results') IS NOT NULL DROP TABLE #Results;
WITH PartialResults AS (
SELECT
cus.CompanyName,
kc.SalesItemId,
n.ItemName AS HeaderName,
SUM([Solgt i perioden KG]) as [Solgt i perioden KG],
SUM([Ultimo lager KG]) as [Ultimo lager KG],
SUM([Primo lager KG]) as [Primo lager KG],
SUM([Købt i perioden KG]) as [Købt i perioden KG]
FROM [dbo].[CustomerSetup] cus
INNER JOIN [dbo].[KeyFigures_Calculation] kc
ON trim(kc.dataname) = cus.dataname -- 'trim' is not a recognized built-in function name.
INNER JOIN KeyFigures_SalesItemNames n
ON n.id = kc.SalesItemId
WHERE cus.HAVI_ID <> 0 --and not kc.SalesItemId is null
GROUP BY cus.CompanyName, kc.SalesItemId, n.ItemName
)
SELECT
CompanyName,
SalesItemId,
HeaderName,
... -- calculations in here
INTO #Results
FROM PartialResultsSELECT *
FROM #Results
ORDER BY
CompanyName,
SalesItemId,
HeaderName“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Hi,
I finally succed in getting the correct data which needs to be reversed.
My data
CREATE TABLE mycompanytable2(
CompanyName NVARCHAR(50) NOT NULL PRIMARY KEY
,ItemName NVARCHAR(50) NOT NULL
,Percent NUMERIC(7,5) NOT NULL
);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 1',N'Item 1',0.93895);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 1',N'Item 2',0.97959);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 1',N'Item 3',0.98765);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 1',N'Item 4',0.00000);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 2',N'Item 1',0.98251);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 2',N'Item 2',0.98496);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 2',N'Item 3',0.96667);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 2',N'Item 4',0.00000);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 3',N'Item 1',0.99181);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 3',N'Item 2',0.98000);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 3',N'Item 3',0.99160);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 3',N'Item 4',0.00000);Cuurent ouput
+-------------+----------+---------+
| CompanyName | ItemName | Percent |
+-------------+----------+---------+
| Company 1 | Item 1 | 0.93895 |
| Company 1 | Item 2 | 0.97959 |
| Company 1 | Item 3 | 0.98765 |
| Company 1 | Item 4 | 0.00000 |
| Company 2 | Item 1 | 0.98251 |
| Company 2 | Item 2 | 0.98496 |
| Company 2 | Item 3 | 0.96667 |
| Company 2 | Item 4 | 0.00000 |
| Company 3 | Item 1 | 0.99181 |
| Company 3 | Item 2 | 0.98000 |
| Company 3 | Item 3 | 0.99160 |
| Company 3 | Item 4 | 0.00000 |
+-------------+----------+---------+needs to be like this:
+-----------+--------+--------+--------+--------+
| Company | Item 1 | Item 2 | Item 3 | Item 4 |
+-----------+--------+--------+--------+--------+
| Company 1 | 0,93% | 0,97% | 0,98% | 0,13% |
| Company 2 | 8,56% | 0,89% | 0,87% | 0,58% |
+-----------+--------+--------+--------+--------+There can be more Companies and more items.
Can you help on this last part?
The comversion of the percent is not needed. -
DROP TABLE #mycompanytable2;
CREATE TABLE #mycompanytable2 (
CompanyName NVARCHAR(50) NOT NULL
,ItemName NVARCHAR(50) NOT NULL
,[Percent] NUMERIC(7,5) NOT NULL
);
INSERT INTO #mycompanytable2(CompanyName, ItemName, [Percent])
VALUES (N'Company 1',N'Item 1',0.93895),
(N'Company 1',N'Item 2',0.97959),
(N'Company 1',N'Item 3',0.98765),
(N'Company 1',N'Item 4',0.00000),
(N'Company 2',N'Item 1',0.98251),
(N'Company 2',N'Item 2',0.98496),
(N'Company 2',N'Item 3',0.96667),
(N'Company 2',N'Item 4',0.00000),
(N'Company 3',N'Item 1',0.99181),
(N'Company 3',N'Item 2',0.98000),
(N'Company 3',N'Item 3',0.99160),
(N'Company 3',N'Item 4',0.00000);-- Always write out at least part of the crosstab query, it gives
-- you something to compare your dynamic sql against.
SELECT CompanyName,
[Item 1] = MAX(CASE WHEN ItemName = 'Item 1' THEN [Percent] ELSE 0 END),
[Item 2] = MAX(CASE WHEN ItemName = 'Item 2' THEN [Percent] ELSE 0 END),
[Item 3] = MAX(CASE WHEN ItemName = 'Item 3' THEN [Percent] ELSE 0 END),
[Item 4] = MAX(CASE WHEN ItemName = 'Item 4' THEN [Percent] ELSE 0 END)
FROM #mycompanytable2
GROUP BY CompanyNameDECLARE
@SQLStart VARCHAR(8000) = 'SELECT CompanyName',
@SQLMiddle VARCHAR(8000) = '',
@SQLEnd VARCHAR(8000) = CHAR(10) + 'FROM #mycompanytable2 GROUP BY CompanyName'-- Build the column list
SELECT @SQLMiddle = @SQLMiddle + ', ' + CHAR(10) + ' ['+ItemName+'] = MAX(CASE WHEN ItemName = ''' + ItemName + ''' THEN [Percent] ELSE 0 END)'
FROM #mycompanytable2 GROUP BY ItemName-- Nicely formatted output for checking
PRINT @SQLStart + @SQLMiddle + @SQLEnd-- Execute the dynamic query
EXEC (@SQLStart + @SQLMiddle + @SQLEnd)“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden - peter larsen-490879 - Tuesday, May 23, 2017 1:20 AM
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply