December 11, 2015 at 4:26 am
zerbit (7/18/2014)
I dislike the solution proposed, it's far too complicated to reach a so easy task.You requirement was to print 3 lots at a time per line of document. This mean that you should have a table with lot associated with the Line, a simple left join will suffice.
Surely the population of this table must be dynamic and should be included in your "simpler" solution? Suppose only a subset of the lot numbers need to be printed. Then there will be gaps, .e.g.
C
D F
G H I
J K
M N O
December 11, 2015 at 6:40 am
Unfortunately no, but what I have discovered is it can be done without cursors. Pseudo code is use a select to build your create Table statement, then an Insert to insert your primary keys, then an Update to stick the values into the rows.
Random numbers of columns is not easy to do in T-SQL.
But if you know the number of columns- Pivot/Unpivot is easier than the above approach.
Ted Seeber
I find your secrets in your data.
http://www.informaitonr.us
December 11, 2015 at 7:02 am
You could add a category column and pivot
with cte
as
(
select LotNo
, ( row_number() over ( order by LotNo ) + 2 ) % 3 as Remainder
, ( row_number() over ( order by LotNo ) - 1) / 3 as Category
from lotno
)
select Category, [0] as column1, [1] as column2, [2] as column3
from cte
pivot
(
min(LotNo) for Remainder in ([0],[1],[2])
) as piv
Returns
Categorycolumn1column2column3
0ABC
1DEF
2GHI
3JKL
4MNO
5PQR
6STU
7VWX
8YZNULL
December 11, 2015 at 10:39 am
Anyone remember Steve Dassin and his set of stored procedure and functions call RAC? It handled all of these crosstab and hierarchy questions. You pass in the base info and it created the correctly built crosstab every time. I bought it back in 2000 and still use it today on SQL 2012, I'd suggest if you can find him grab this up, it's simple and flat works.
http://forums.databasejournal.com/showthread.php?9932-Crosstab-pivoting-utility-for-sql-server-2000
Chuck
December 11, 2015 at 1:46 pm
elghazal.med (12/11/2015)
Hello,I already worked on this. But i used another tool which is Stata to convert both column to rows and rows to column and i used TOS to insert Data in DB. It is great and easy.
It also adds to the proverbial "Tower of Babel". Why would you use an external 3rd Party tool to do something that's so simple to do where the data is actually stored at?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2015 at 1:48 pm
johnbrown105 56149 (12/11/2015)
zerbit (7/18/2014)
I dislike the solution proposed, it's far too complicated to reach a so easy task.You requirement was to print 3 lots at a time per line of document. This mean that you should have a table with lot associated with the Line, a simple left join will suffice.
Surely the population of this table must be dynamic and should be included in your "simpler" solution? Suppose only a subset of the lot numbers need to be printed. Then there will be gaps, .e.g.
C
D F
G H I
J K
M N O
You would simply apply the filter that defines the subset where the rows are numbered and then you won't have that problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2015 at 1:51 pm
fregatepllada (7/17/2014)
Thanks Jeff - I did CROSSTAB implementation before SQL 2005.For my current project I stick with PIVOT (I have less than 10K rows). 😉
Heh... I love it when people justify slower code based on lower rowcounts and then scream bloody murder when they need to do something similar with much larger row counts. Get used to doing it the fastest way possible all the time and then you'll only need to remember one way. NEVER justify slow or resource intensive code based on low row counts because you can't guarantee that no one will use your code for something bigger. In most cases, you usually can't even guarantee that the tables that you currently know to be small will never grow.
It takes so little time to do it right. Do it right all the time. No excuses. If you do it right all the time, you'll also get much faster at it.;-)
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2015 at 2:22 pm
[p]
This seems like an interesting case for a PIVOT example as a way top promote thinking in a more relational sense. Of course, this makes the assumption that one is using a version providing PIVOT.
[/p]
[p]
Forgive the omission of the table set up -- and my use of temp tables instead or real ones. The original data table is exactly like the table used in article's example.
[/p]
CREATE TABLE #ColumnNames (ColumnKey int, Columnname VARCHAR(10))
DELETE #ColumnNames
INSERT INTO #ColumnNames (ColumnKey, ColumnName)
Values (0, 'Column1')
, (1, 'Column2')
, (2, 'Column3')
SELECT max(column1) Lot1, max(column2) Lot2, max(column3) Lot3 FROM (
SELECT Source.LotNo, source.ColumnKey, Source.RowKey, map.Columnname FROM (
SELECT LotNo
, (Row_Number() over (order by LotNo) + 2) / 3 as RowKey
, (Row_Number() over (order by LotNo) - 1) % 3 as ColumnKey
FROM #OriginalResultSet
) Source
INNER JOIN #ColumnNames Map on Map.ColumnKey = Source.ColumnKey %3
) AS ResultSet
PIVOT (
MAX (LotNo) FOR ColumnName in (Column1, Column2, Column3)
) as it
group by RowKey
December 11, 2015 at 5:30 pm
vjonnal1 (7/17/2014)
I am bit confused here, what's preventing you to use PIVOT function here?
Performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2015 at 5:37 pm
fregatepllada (7/17/2014)
Luis, if we talking about performance difference did you try to benchmark your approach vs PIVOT?IFAIK PIVOT became part of built-in SQL Server functionality since SQL Server 2005. 😉
Not all that is "new" is better. PIVOT is slower than a CROSSTAB. Since no one that's asking this question or posting PIVOT code actually appear to have read the article I provided a link to about how CROSS TAB beats PIVOT, I'm setting up a larger test that will include all posted code that works to demonstrate why you should consider NOT using PIVOT. Then they can finally go back and read the article I provided a link to and see what some of the other advantages of CROSSTAB are.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2015 at 5:39 pm
fregatepllada (7/17/2014)
Thanks Luis, I hate to bring another dimension to performance test but I am using TABLE variable, not temporary table. 😉
Where's that code? I'll add it to the test I'm setting up...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2015 at 5:46 pm
zerbit (7/18/2014)
I dislike the solution proposed, it's far too complicated to reach a so easy task.You requirement was to print 3 lots at a time per line of document. This mean that you should have a table with lot associated with the Line, a simple left join will suffice.
If your table looks like this
IDNoLotNoSaleOrdLine1A1
2B1
3C1
4D2
5E2
6F2
7G2
8H2
9I2
10J2
11K3
12L3
13M3
14N3
15O3
16P3
17Q3
18R3
19S3
20T3
21U3
22V3
23W3
24X3
25Y3
26Z3
select L1.SaleOrdLine,L1.LotNo as L1,coalesce(L2.LotNo,'') as L2,coalesce(L3.LotNo,'') as L3from [LotNo] L1
left join [LotNo] L2 on L1.saleOrdLine=L2.SaleOrdLine and L2.idno=L1.IDNo+1
left join [LotNo] L3 on L2.saleOrdLine=L3.SaleOrdLine and L3.idno=L2.IDNo+1
where L1.IDNo%3=1
with this results:
SaleOrdLineL1L2L31ABC
2DEF
2GHI
2J
3MNO
3PQR
3STU
3VWX
3YZ
Best regards,
PS: your solution is indeed good, but for more complex applications.
You say the other solution is too complex but where is YOUR code to create the SaleOrdLine column? And do you really think that 3 scans of a table will be faster? If so (and I know you might not be around after a year), post the code to update the SaleOrdLine column and I'll be happy to include it in the test I'm building. As they say, "one test is worth a thousand expert opinions". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2015 at 5:56 pm
steven.bingham (7/18/2014)
You're usage of row_number(), modulo and such is impressive, but Imust admit that I found the solution a bit over complex and it was making
my head hurt a bit.
Consider the following code, the key is using the COALESCE statement, the
example assumes using the original 'LOTNO' table populated in the post:
DECLARE @returnResult varchar(255)
, @StartLotNumber int = 0
, @LastLotNumber int = 13
SELECT @returnResult = COALESCE(@returnResult + ',', '') + lot.LotNo, @LastLotNumber = IDNo
FROM dbo.LotNo lot
WHERE IDNo BETWEEN @StartLotNumber AND @LastLotNumber
SELECT 'Lots for Line Item#1: ' + @returnResult
select @StartLotNumber = @LastLotNumber + 1, @LastLotNumber = @StartLotNumber + 13, @returnResult = ''
SELECT @returnResult = COALESCE(@returnResult + ',', '') + lot.LotNo, @LastLotNumber = IDNo
FROM dbo.LotNo lot
WHERE IDNo BETWEEN @StartLotNumber AND @LastLotNumber
SELECT 'Lots for Line Item#2: ' + @returnResult
OUTPUT RESULTS
Lots for Line Item#1: A,B,C,D,E,F,G,H,I,J,K,L,M
Lots for Line Item#2: ,N,O,P,Q,R,S,T,U,V,W,X,Y,Z
But that doesn't create the same output. You're just concatenating data and if the data isn't all the same length, it'll be a mess besides. I'm not including this in the test because it doesn't meet the objective in the article.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2015 at 5:58 pm
seebert42 (12/11/2015)
Unfortunately no, but what I have discovered is it can be done without cursors. Pseudo code is use a select to build your create Table statement, then an Insert to insert your primary keys, then an Update to stick the values into the rows.Random numbers of columns is not easy to do in T-SQL.
But if you know the number of columns- Pivot/Unpivot is easier than the above approach.
I'll have to respectfully disagree on all counts but, even if it was, PIVOT is slower than CROSSTAB.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2015 at 6:55 pm
Columnar output that is row-ordered is difficult to read, so I worked on a variation of the requirement - column-order sorting, with a flexible number of columns. In the code below, the number of cols can be set anywhere from 1 to 6. I've split the code into multiple stages of CTEs because my TSQL-foo is weak. I am sure there are better solutions. (I renamed the table to "Lot" to avoid the confusion of "LotNo" being the name of both table and column).
If you have four columns, my target results would be:
AHOV
BIPW
CJQX
DKRY
ELSZ
FMT
GNU
declare @NumCols int = 4
declare @NumRows int
select @NumRows = (COUNT(*)+(@NumCols-1))/@NumCols from lot;
raiserror( '@NumRows=%i',0,0,@NumRows)
;
with Data1 as
(
SELECT LotNo, ROW_NUMBER() OVER(ORDER BY LotNo) AS RowNum
from Lot
),
Data2 as
(
select LotNo, RowNum, (RowNum+@NumRows-1)/@NumRows as ColNum
FROM Data1
),
Data3 as
(
select LotNo, RowNum, Colnum, ROW_NUMBER() OVER(PARTITION BY Colnum ORDER BY RowNum) AS ColRow
FROM Data2
),
Data4 as
(
select LotNo, RowNum, ColNum, ColRow, (ColRow-1)*@NumCols+ColNum as SortNum from data3
)
SELECT
MAX(CASE WHEN (@NumCols > 0) and (SortNum % @NumCols = 0) THEN LotNo ELSE '' END) AS [Col1],
MAX(CASE WHEN (@NumCols > 1) and (SortNum % @NumCols = 1) THEN LotNo ELSE '' END) AS [Col2],
MAX(CASE WHEN (@NumCols > 2) and (SortNum % @NumCols = 2) THEN LotNo ELSE '' END) AS [Col3],
MAX(CASE WHEN (@NumCols > 3) and (SortNum % @NumCols = 3) THEN LotNo ELSE '' END) AS [Col4],
MAX(CASE WHEN (@NumCols > 4) and (SortNum % @NumCols = 4) THEN LotNo ELSE '' END) AS [Col5],
MAX(CASE WHEN (@NumCols > 5) and (SortNum % @NumCols = 5) THEN LotNo ELSE '' END) AS [Col6]
FROM (
-- add row number to base data. Start at 3 so that first value % 3 = 0 (column 1)
(SELECT top 1000 LotNo, ColRow, SortNum-1 as SortNum FROM Data4 order by SortNum)
) AS derived
GROUP BY ColRow
If I set numer of columns 6 , I get
AFKPUZ
BGLQV
CHMRW
DINSX
EJOTY
Viewing 15 posts - 31 through 45 (of 58 total)
You must be logged in to reply to this topic. Login to reply