Please help with the T-SQL. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Please help with the T-SQL.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-02-11 : 17:26:19

[code]IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test]') AND type in (N'U'))
DROP TABLE [dbo].[Test]
GO

CREATE TABLE [dbo].[Test](
[CustomerCode] [char](3) NOT NULL
) ON [PRIMARY]

GO

Give the table below. How can I obtain the new values below.

Thanks so much.

-- DELETE Test;

INSERT Test (CustomerCode)
VALUES ('ABC'), ('DEF'), ('GHI');
GO

SELECT *
FROM Test;
go

--Result want:

NewCustomerCode
---------------
ABC001
ABC002
ABC003
ABC004
ABC005

DEF001
DEF002
DEF003

GHI001
GHI002
GHI003
GHI004[/code]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-11 : 17:40:05

[code]

DECLARE @Test TABLE(
[CustomerCode] [char](3) NOT NULL
)

INSERT INTO @Test (CustomerCode)
SELECT 'ABC' UNION ALL
SELECT 'ABC' UNION ALL
SELECT 'ABC' UNION ALL
SELECT 'ABC' UNION ALL
SELECT 'ABC' UNION ALL
SELECT 'ABC' UNION ALL
SELECT 'DEF' UNION ALL
SELECT 'DEF' UNION ALL
SELECT 'DEF' UNION ALL
SELECT 'DEF' UNION ALL
SELECT 'GHI' UNION ALL
SELECT 'GHI' UNION ALL
SELECT 'GHI'

SELECT * FROM @test
;WITH cteTEST AS
(SELECT CustomerCode,
ROWNUM = Row_Number() OVER( ORDER BY CustomerCode)
FROM @Test
)

SELECT CustomerCode + RIGHT(REPLICATE('0',3) + CAST(ROWNUM as varchar(3)),3)
FROM cteTEST

[/code]

Poor planning on your part does not constitute an emergency on my part.

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-02-11 : 17:48:41

[code]When I ran your query, I got the results below but not quite what I expecting. Thanks.

------
ABC001
ABC002
ABC003
ABC004
ABC005
ABC006

DEF007 --> should be DEF001
DEF008 DEF002
DEF009 DEF003
DEF010 DEF004

GHI011 GHI001
GHI012 GHI002
GHI013 GHI003[/code]

quote:
Originally posted by dataguru1971

[code]

DECLARE @Test TABLE(
[CustomerCode] [char](3) NOT NULL
)

INSERT INTO @Test (CustomerCode)
SELECT 'ABC' UNION ALL
SELECT 'ABC' UNION ALL
SELECT 'ABC' UNION ALL
SELECT 'ABC' UNION ALL
SELECT 'ABC' UNION ALL
SELECT 'ABC' UNION ALL
SELECT 'DEF' UNION ALL
SELECT 'DEF' UNION ALL
SELECT 'DEF' UNION ALL
SELECT 'DEF' UNION ALL
SELECT 'GHI' UNION ALL
SELECT 'GHI' UNION ALL
SELECT 'GHI'

SELECT * FROM @test
;WITH cteTEST AS
(SELECT CustomerCode,
ROWNUM = Row_Number() OVER( ORDER BY CustomerCode)
FROM @Test
)

SELECT CustomerCode + RIGHT(REPLICATE('0',3) + CAST(ROWNUM as varchar(3)),3)
FROM cteTEST

[/code]

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-11 : 18:01:35

Sorry about that! You have add the PARTITION BY clause ..misread the original request.


 


DECLARE @Test TABLE(
	[CustomerCode] [char](3) NOT NULL
)  


 
 INSERT INTO  @Test (CustomerCode)
 SELECT 'ABC' UNION ALL
 SELECT 'ABC' UNION ALL
 SELECT 'ABC' UNION ALL
 SELECT 'ABC' UNION ALL
 SELECT 'ABC' UNION ALL
 SELECT 'ABC' UNION ALL
 SELECT 'DEF' UNION ALL
 SELECT 'DEF' UNION ALL
 SELECT 'DEF' UNION ALL
 SELECT 'DEF' UNION ALL
 SELECT 'GHI' UNION ALL
SELECT 'GHI' UNION ALL
SELECT 'GHI'  

SELECT * FROM @test
;WITH cteTEST AS
	(SELECT CustomerCode,
			ROWNUM = Row_Number() OVER( PARTITION BY CustomerCode ORDER BY CustomerCode)
	 FROM @Test
	)

SELECT CustomerCode + RIGHT(REPLICATE('0',3) + CAST(ROWNUM as varchar(3)),3)
FROM cteTEST

Poor planning on your part does not constitute an emergency on my part.

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-02-11 : 18:01:37

I got it.  Thanks.

here is the code.

DROP TABLE dbo.NewPools;
go
CREATE TABLE dbo.NewPools
(
 [CustomerCode] [char](3) NOT NULL
) 
GO
 
 INSERT INTO  NewPools (CustomerCode)
 SELECT 'ABC' UNION ALL
 SELECT 'ABC' UNION ALL
 SELECT 'ABC' UNION ALL
 SELECT 'ABC' UNION ALL
 SELECT 'ABC' UNION ALL
 SELECT 'ABC' UNION ALL
 SELECT 'DEF' UNION ALL
 SELECT 'DEF' UNION ALL
 SELECT 'DEF' UNION ALL
 SELECT 'DEF' UNION ALL
 SELECT 'GHI' UNION ALL
SELECT 'GHI' UNION ALL
SELECT 'GHI';
go

    SELECT *
      FROM NewPools;
    go
    
CustomerCode
------------
ABC
ABC
ABC
ABC
ABC
ABC
DEF
DEF
DEF
DEF
GHI
GHI
GHI    
    
;WITH cteTEST AS
	( SELECT CustomerCode,
			ROW_NUMBER () OVER ( PARTITION BY CustomerCode ORDER BY CustomerCode ASC) AS 'RowNum'
	   FROM NewPools
	)

   SELECT CustomerCode + RIGHT(REPLICATE('0',3) + CAST(RowNum as varchar(3)),3)
     FROM cteTEST
   go

ABC001
ABC002
ABC003
ABC004
ABC005
ABC006
DEF001
DEF002
DEF003
DEF004
GHI001
GHI002
GHI003


--------------------------

quote:

Originally posted by dataguru1971


 


DECLARE @Test TABLE(
	[CustomerCode] [char](3) NOT NULL
)  


 
 INSERT INTO  @Test (CustomerCode)
 SELECT 'ABC' UNION ALL
 SELECT 'ABC' UNION ALL
 SELECT 'ABC' UNION ALL
 SELECT 'ABC' UNION ALL
 SELECT 'ABC' UNION ALL
 SELECT 'ABC' UNION ALL
 SELECT 'DEF' UNION ALL
 SELECT 'DEF' UNION ALL
 SELECT 'DEF' UNION ALL
 SELECT 'DEF' UNION ALL
 SELECT 'GHI' UNION ALL
SELECT 'GHI' UNION ALL
SELECT 'GHI'  

SELECT * FROM @test
;WITH cteTEST AS
	(SELECT CustomerCode,
			ROWNUM = Row_Number() OVER( ORDER BY CustomerCode)
	 FROM @Test
	)

SELECT CustomerCode + RIGHT(REPLICATE('0',3) + CAST(ROWNUM as varchar(3)),3)
FROM cteTEST

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources