SQL Server: add reference number according to other fields in record
I would like to ask a SQL question (I am currently using SQL Server Management Studio).
CustomerID ProductID ProductName
------------------------------------------------------
111 6577 ProductA
111 6123 ProductB
111 1133 ProductC
111 1133 ProductC
222 6577 ProductA
222 6577 ProductA
222 1578 ProductL
333 9273 ProductX
and for the result it will depend on the customerId and the product to add the reference number to another column (for the same customer ID if the product ID is different from the reference number)
CustomerID ProductID ProductName ref
-----------------------------------------------------------
111 6577 ProductA 111-1
111 6123 ProductB 111-2
111 1133 ProductC 111-3
111 1133 ProductC 111-3
222 6577 ProductA 222-1
222 6577 ProductA 222-1
222 1578 ProductL 222-2
333 9273 ProductX 333-1
I'm not sure how to add a reference number comparing productid?
Thank you in advance
source to share
using : dense_rank()
select *
, ref = convert(varchar(13),customerid) + '-'
+ convert(varchar(13),dense_rank() over (partition by customerid order by productname))
from t
registry: http://rextester.com/UBXR81287
returns:
+------------+-----------+-------------+-------+
| customerid | productid | productname | ref |
+------------+-----------+-------------+-------+
| 111 | 6577 | ProductA | 111-1 |
| 111 | 6123 | ProductB | 111-2 |
| 111 | 1133 | ProductC | 111-3 |
| 111 | 1133 | ProductC | 111-3 |
| 222 | 6577 | ProductA | 222-1 |
| 222 | 6577 | ProductA | 222-1 |
| 222 | 1578 | ProductL | 222-2 |
| 333 | 9273 | ProductX | 333-1 |
+------------+-----------+-------------+-------+
source to share
try it
DECLARE @sampledata AS TABLE
(
CustomerID int,
ProductID int
)
INSERT INTO @sampledata VALUES (111, 6577),(111,6123 ),(111,1133 ),(111 ,1133)
SELECT *,
CONCAT(s.CustomerID,'-',CAST(dense_rank() over(PARTITION BY s.CustomerID ORDER BY s.ProductID DESC) AS varchar(10))) AS ref
FROM @sampledata s
source to share
you can use dense_rank () function
select *, concat(customerId, '-', dense_rank() over(partition by Customerid order by ProductName)) from #yourCustomer
Your table:
create table #yourCustomer (CustomerId int, ProductId int, ProductName varchar(20))
insert into #yourCustomer (CustomerId, ProductId, ProductName) values
(111 , 6577 ,'ProductA')
,(111 , 6123 ,'ProductB')
,(111 , 1133 ,'ProductC')
,(111 , 1133 ,'ProductC')
,(222 , 6577 ,'ProductA')
,(222 , 6577 ,'ProductA')
,(222 , 1578 ,'ProductL')
,(333 , 9273 ,'ProductX')
source to share
The dense rank answers are very good and provide exactly what you asked for.
But there is potential banana skin; If you introduce a new product, at some point in the future, and then run the logic Ref
, you will get different results.
If the format Ref
doesn't matter, consider concatenating CustomerId
and ProductId
. Examples: 111-1 becomes 111-6577. 222-2 becomes 222-1578.
If the format is important, think about creating a table, the Product ( ProductId
, ProductName
, ProductRef
). Ref
then it will be CustomerId
+ -
+ ProductRef
. Example:
ProductId ProductName ProductRef
6577 ProductA 1
6123 ProductB 2
1133 ProductC 3
source to share