Showing posts with label int. Show all posts
Showing posts with label int. Show all posts

Thursday, March 22, 2012

Conditions on latest record

I have a table that has records layed out as so:

Table:
fd_Id INT IDENTITY (1, 1)
fd_User VARCHAR(30)
fd_Effective DATETIME

Data could be as follows:
1 | "user1" | 6/20/2001
2 | "user2" | 6/1/2002
3 | "user2" | 6/5/2002
4 | "user2" | 6/5/2002
5 | "user2" | 2/1/2002
6 | "user3" | 9/1/2003
7 | "user3" | 10/2/2002
8 | "user4" | 1/1/2005

What I need to retrieve from that table is the SINGLE LATEST item of
each fd_User.

Results:
1 | "user1" | 6/20/2001
3 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates are
the same but only 1 of them
6 | "user3" | 9/1/2003
8 | "user4" | 1/1/2005Untested

SELECT
MAX(FD_ID) AS 'FD_ID',
FD_USER,
MAX(FD_EFFECTIVE) AS 'FD_EFFECTIVE'
FROM F_TABLE
GROUP FD_USER|||select min(a.fd_Id) as fd_Id,
a.fd_User,
a.fd_Effective
from mytable a
inner join (select fd_User,max(fd_Effective) as fd_Effective
from mytable
group by fd_User) b on a.fd_User=b.fd_User and
a.fd_Effective=b.fd_Effective
group by a.fd_User,a.fd_Effective|||Verticon:: wrote:
> I have a table that has records layed out as so:
> Table:
> fd_Id INT IDENTITY (1, 1)
> fd_User VARCHAR(30)
> fd_Effective DATETIME
> Data could be as follows:
> 1 | "user1" | 6/20/2001
> 2 | "user2" | 6/1/2002
> 3 | "user2" | 6/5/2002
> 4 | "user2" | 6/5/2002
> 5 | "user2" | 2/1/2002
> 6 | "user3" | 9/1/2003
> 7 | "user3" | 10/2/2002
> 8 | "user4" | 1/1/2005
> What I need to retrieve from that table is the SINGLE LATEST item of
> each fd_User.
> Results:
> 1 | "user1" | 6/20/2001
> 3 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates are
> the same but only 1 of them
> 6 | "user3" | 9/1/2003
> 8 | "user4" | 1/1/2005

First add the constraint that you're apparently missing:

ALTER TABLE tbl
ADD CONSTRAINT ak1_tbl
UNIQUE (fd_User, fd_Effective);

Then:

SELECT fd_Id, fd_User, fd_Effective
FROM tbl
WHERE fd_Effective =
(SELECT MAX(fd_Effective)
FROM tbl AS t
WHERE t.fd_User = tbl.fd_User);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--sqlsql

Thursday, March 8, 2012

Conditional Joins

Hi all,

I have 4 tables with the structure shown below

Main Table :

Create Table TestMain
(TestMainId INT , TestCompanyID INT )

Other Tables :

Create Table TestCompany1
(Id INT , TestCompanyID INT )

Create Table TestCompany2
(Id INT , TestCompanyID INT )

Create Table TestCompany3
(Id INT , TestCompanyID INT )

In this above tables.. I would have a record in the table TestMain and a entry for that specific record would be in any of the tables like TestCompany1,TestCompany2,TestCompany3

Sample Records :

In the table TestMain

1 1000
2 2000
3 3000
4 4000
5 5000
6 6000
7 7000

In the table TestCompany1

1 1000
2 6000

In the table TestCompany2

1 3000
2 4000
3 5000

In the table TestCompany3

1 7000

How do I join those tables and fetch the main record with its subsequent entry from the other tables ?

Thanks in advance,

HHADo you have defined any relationship between the tables..? It's basic requirement for data integrity.

Anyway you can join the two tables this way...

Select Testmain.TestMainId, TestMain.TestCompanyID From TestMain
JOIN TestCompany1 ON TestMain.TestCompanyID = TestCompany1.TestCompanyID

You can join more than two tables using different join types...|||Hi

You would need to use left joins and maybe COALESCE but it is hard to know without more details. The fact that you are doing this hints that your design may not be sound too (although it may be - this looks like a mock up yes?).

HTH|||I think your best bet would be to inner join against each table and UNION or UNION ALL the results - your design does look dubious though, why are you segmenting companies across 3 tables - do they have different attributes per collection or is there another reason?|||Hi all,

There is a main table say COMPANY and they other tables CompanyA , CompanyB , CompanyC.

The main table COMPANY has the general info about the company ( like address , contact info) and there are 3 BIT columns to indicate what all type of company it falls under.If it falls under A & B , then the relevant information
are stored in CompanyA & CompanyB.

Now I need to write a proc which gets few input parameters and searches
for the company details.

1. If no parameters where passed , I need to get all the company from the
COMPANY with relevant information from the CompanyA, CompanyB,CompanyC.

2.If I get a parameter which says I should fetch only companys falling under
CompanyA , I should be able to get them too.

Still , the DB is in production , I cant touch the design.

Thanks for all your help ,

HHA|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71565

Terrible design - I'm sure you know or are at least becoming aware of.

Playing with post three from the link (not efficient - there may be better solutions):

CREATE TABLE #TESTMAIN
(TESTMAINID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY1
(ID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY2
(ID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY3
(ID INT , TESTCOMPANYID INT )

INSERT #TESTMAIN
SELECT 1, 1000 UNION ALL
SELECT 2, 2000 UNION ALL
SELECT 3, 3000 UNION ALL
SELECT 4, 4000 UNION ALL
SELECT 5, 5000 UNION ALL
SELECT 6, 6000 UNION ALL
SELECT 7, 7000

INSERT #TESTCOMPANY1
SELECT 1, 1000 UNION ALL
SELECT 2, 6000

INSERT #TESTCOMPANY2
SELECT 1, 3000 UNION ALL
SELECT 2, 4000 UNION ALL
SELECT 3, 5000

INSERT #TESTCOMPANY3
SELECT 1, 7000

DECLARE @.CompanyOneOnly AS Bit
SET @.CompanyOneOnly = 1

SELECT *
FROM -- Relvent companys
(SELECT X1.TESTMAINID,
X1.TESTCOMPANYID,
ISNULL(X2.ID,0) AS Comp1,
ISNULL(X3.ID,0) AS Comp2,
ISNULL(X4.ID,0) AS Comp3
FROM #TESTMAIN X1
LEFT JOIN #TESTCOMPANY1 X2 ON X1.TESTCOMPANYID = X2.TESTCOMPANYID
LEFT JOIN #TESTCOMPANY2 X3 ON X1.TESTCOMPANYID = X3.TESTCOMPANYID
LEFT JOIN #TESTCOMPANY3 X4 ON X1.TESTCOMPANYID = X4.TESTCOMPANYID) AS DerT
WHERE CAST(Comp1 AS Bit) = @.CompanyOneOnly OR @.CompanyOneOnly = 0

DROP TABLE #TESTMAIN
DROP TABLE #TESTCOMPANY1
DROP TABLE #TESTCOMPANY2
DROP TABLE #TESTCOMPANY3

Saturday, February 25, 2012

Conditional Constraint - HowTo

In the table below:
===========
Roles
===========
UserID (int)
GroupID
===========
Considering that UserID is FK from Users Table and GroupID is FK from Groups
Table:
===========
Groups
===========
GroupID (int)
GroupName
===========
===========
Users
===========
UserID
Username
Password
Email
===========
And considering a User can have 0 or many Roles, exs:
Roles table
===========
1,1
1,2
1,3
===========
Meaning that this user have roles: 1,2,3
The problem is that I can have also:
Roles table
===========
1,1
1,1
1,1
===========
which is bad, so I need to have a constraint in the Roles table to force Gro
upID Unique ONLY in case UserID is already there!
Is it possible?I isn't this just a matter of letting both columns be the primary key?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:131352F2-272C-4666-95A2-E153FB2C4932@.microsoft.com...
> In the table below:
> ===========
> Roles
> ===========
> UserID (int)
> GroupID
> ===========
> Considering that UserID is FK from Users Table and GroupID is FK from Grou
ps Table:
> ===========
> Groups
> ===========
> GroupID (int)
> GroupName
> ===========
>
> ===========
> Users
> ===========
> UserID
> Username
> Password
> Email
> ===========
> And considering a User can have 0 or many Roles, exs:
> Roles table
> ===========
> 1,1
> 1,2
> 1,3
> ===========
> Meaning that this user have roles: 1,2,3
>
> The problem is that I can have also:
> Roles table
> ===========
> 1,1
> 1,1
> 1,1
> ===========
> which is bad, so I need to have a constraint in the Roles table to force GroupID U
nique ONLY in
case UserID is already there!
> Is it possible?|||Hum, but then I believe there′ll be just 1 role per user?
"Tibor Karaszi" wrote:

> I isn't this just a matter of letting both columns be the primary key?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:131352F2-272C-4666-95A2-E153FB2C4932@.microsoft.com...
> case UserID is already there!
>
>|||No, this enforces that you cannot have a duplicate of the *combination* of t
he columns. So, below is
fine:
1,2
1,3
2,2
2,3
Below is not fine:
1,2
1,2
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:7BF6E135-7CE1-4370-BF78-51032E0D8AFB@.microsoft.com...[vbcol=seagreen]
> Hum, but then I believe therell be just 1 role per user?
> "Tibor Karaszi" wrote:
>
in[vbcol=seagreen]|||Actually your Right! Sorry for misunderstanding and thanks very much for the
quick response :-)
"Tibor Karaszi" wrote:

> No, this enforces that you cannot have a duplicate of the *combination* of
the columns. So, below is
> fine:
> 1,2
> 1,3
> 2,2
> 2,3
>
> Below is not fine:
> 1,2
> 1,2
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:7BF6E135-7CE1-4370-BF78-51032E0D8AFB@.microsoft.com...
> in
>
>|||We all lose ourselves from time to time. Glad it helped. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:60CD2318-C000-4EC4-9C76-992096AD5F71@.microsoft.com...[vbcol=seagreen]
> Actually your Right! Sorry for misunderstanding and thanks very much for t
he quick response :-)
> "Tibor Karaszi" wrote:
>
below is[vbcol=seagreen]
ONLY[vbcol=seagreen]

Conditional Constraint - HowTo

In the table below:
===========
Roles
===========
UserID (int)
GroupID
===========
Considering that UserID is FK from Users Table and GroupID is FK from Groups Table:
===========
Groups
===========
GroupID (int)
GroupName
===========
===========
Users
===========
UserID
Username
Password
Email
===========
And considering a User can have 0 or many Roles, exs:
Roles table
===========
1,1
1,2
1,3
===========
Meaning that this user have roles: 1,2,3
The problem is that I can have also:
Roles table
===========
1,1
1,1
1,1
===========
which is bad, so I need to have a constraint in the Roles table to force GroupID Unique ONLY in case UserID is already there!
Is it possible?
I isn't this just a matter of letting both columns be the primary key?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:131352F2-272C-4666-95A2-E153FB2C4932@.microsoft.com...
> In the table below:
> ===========
> Roles
> ===========
> UserID (int)
> GroupID
> ===========
> Considering that UserID is FK from Users Table and GroupID is FK from Groups Table:
> ===========
> Groups
> ===========
> GroupID (int)
> GroupName
> ===========
>
> ===========
> Users
> ===========
> UserID
> Username
> Password
> Email
> ===========
> And considering a User can have 0 or many Roles, exs:
> Roles table
> ===========
> 1,1
> 1,2
> 1,3
> ===========
> Meaning that this user have roles: 1,2,3
>
> The problem is that I can have also:
> Roles table
> ===========
> 1,1
> 1,1
> 1,1
> ===========
> which is bad, so I need to have a constraint in the Roles table to force GroupID Unique ONLY in
case UserID is already there!
> Is it possible?
|||Hum, but then I believe there′ll be just 1 role per user?
"Tibor Karaszi" wrote:

> I isn't this just a matter of letting both columns be the primary key?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:131352F2-272C-4666-95A2-E153FB2C4932@.microsoft.com...
> case UserID is already there!
>
>
|||No, this enforces that you cannot have a duplicate of the *combination* of the columns. So, below is
fine:
1,2
1,3
2,2
2,3
Below is not fine:
1,2
1,2
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:7BF6E135-7CE1-4370-BF78-51032E0D8AFB@.microsoft.com...[vbcol=seagreen]
> Hum, but then I believe therell be just 1 role per user?
> "Tibor Karaszi" wrote:
in[vbcol=seagreen]
|||Actually your Right! Sorry for misunderstanding and thanks very much for the quick response :-)
"Tibor Karaszi" wrote:

> No, this enforces that you cannot have a duplicate of the *combination* of the columns. So, below is
> fine:
> 1,2
> 1,3
> 2,2
> 2,3
>
> Below is not fine:
> 1,2
> 1,2
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:7BF6E135-7CE1-4370-BF78-51032E0D8AFB@.microsoft.com...
> in
>
>
|||We all lose ourselves from time to time. Glad it helped. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:60CD2318-C000-4EC4-9C76-992096AD5F71@.microsoft.com...[vbcol=seagreen]
> Actually your Right! Sorry for misunderstanding and thanks very much for the quick response :-)
> "Tibor Karaszi" wrote:
below is[vbcol=seagreen]
ONLY[vbcol=seagreen]

Friday, February 24, 2012

Condensed Type vs Data Type

In the database diagram, it used to be that Data Type was int, varchar, char, etc. and Condensed Type was int, varchar(30), char(5), etc. Why was this changed in SQL 2005? Is this a bug?

My assumption is that it's because of the new data type "xml". The help says as follows:-

Condensed Data Type

Displays information about the field's data type, in the same format as the SQL CREATE TABLE statement. For example, a field containing a variable-length string with a maximum length of 20 characters would be represented as "varchar(20)". To change this property, type the value directly.

ym

Condensed Type vs Data Type

In the database diagram, it used to be that Data Type was int, varchar, char, etc. and Condensed Type was int, varchar(30), char(5), etc. Why was this changed in SQL 2005? Is this a bug?

My assumption is that it's because of the new data type "xml". The help says as follows:-

Condensed Data Type

Displays information about the field's data type, in the same format as the SQL CREATE TABLE statement. For example, a field containing a variable-length string with a maximum length of 20 characters would be represented as "varchar(20)". To change this property, type the value directly.

ym

Tuesday, February 14, 2012

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advance
perspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4
|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advanceperspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
>> this is my DDL:
>> CREATE TABLE [dbo].[Table1] (
>> [Code] [int] IDENTITY (1, 1) NOT NULL ,
>> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [ParentCode] [int] NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Table1] ADD
>> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>> (
>> [Code]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Table1] ADD
>> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
>> (
>> [ParentCode]
>> ) REFERENCES [dbo].[Table1] (
>> [Code]
>> )
>> I want to concat Column of Name:
>> Code Name ParentCode
>> 1 test NULL
>> 2 book NULL
>> 3 Cake 1
>> 4 Mouse 3
>> I want to concat column of Name for Code=4 and output will be: testCake
>> thanks in advance
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>|||On Apr 30, 9:13 am, "perspolis" <reza...@.hotmail.com> wrote:
> I want to do that for many levels as is not for 2 rows.
> "Ed Enstrom" <nos...@.invalid.net> wrote in message
> news:np8Zh.98$eH4.18@.newsfe12.lga...
>
> > perspolis wrote:
> >> this is my DDL:
> >> CREATE TABLE [dbo].[Table1] (
> >> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> >> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> >> [ParentCode] [int] NULL
> >> ) ON [PRIMARY]
> >> GO
> >> ALTER TABLE [dbo].[Table1] ADD
> >> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> >> (
> >> [Code]
> >> ) ON [PRIMARY]
> >> GO
> >> ALTER TABLE [dbo].[Table1] ADD
> >> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> >> (
> >> [ParentCode]
> >> ) REFERENCES [dbo].[Table1] (
> >> [Code]
> >> )
> >> I want to concat Column of Name:
> >> Code Name ParentCode
> >> 1 test NULL
> >> 2 book NULL
> >> 3 Cake 1
> >> 4 Mouse 3
> >> I want to concat column of Name for Code=4 and output will be: testCake
> >> thanks in advance
> > SELECT c.[Name] + b.[Name] AS ConcatName
> > FROM Table1 AS a
> > JOIN Table1 AS b ON a.ParentCode = b.Code
> > JOIN Table1 AS c ON b.ParentCode = c.Code
> > WHERE a.Code = 4- Hide quoted text -
> - Show quoted text -
If you are using SQL Server 2005 CTE with recursive query.
with temp as
(select convert(varchar(50),'') + convert(varchar(50),'')
name ,parentcode,code from table1 where code =4
union all
select convert(varchar(50),t1.name)+convert(varchar(50),t.name) as
name ,t1.parentcode,t1.code
from table1 t1 inner join temp t on t.parentcode = t1.code)
select name from temp where parentcode is null
Regards
Amish shah
http://shahamishm.tripod.com

Sunday, February 12, 2012

Concatenation Formula For Int Columns

Column A, Column B and Column C : All Integer.

I want a concatenation. For example A=111, B=222. C should be 111222 NOT 333. Is it possible? If it's possible, what is the formula?

Thanks in advance...

If you need to concatenate numeric values, you need toCAST them as character values first.
SELECTCAST(ColumnAAS varchar(10)) +CAST(ColumnBAS varchar(10))AS Column3FROM myTable
|||Thank you but i ask the formula to use in Formula Property of Column in SQL (Enterprise Manager). Isn't that possible?|||Yes. The formula would be exactly the same, without the AS clause. Did you try it?

CAST(ColumnA AS varchar(10)) + CAST(ColumnB AS Varchar(10))|||

Tried after my answer.Smile You were right. Sorry and thank you.Big Smile

|||Cool, I'm glad it worked.|||Out of subject and not so important but i wondered. I set C as Unique. When i insert record, if it's duplicate so rollback transaction but ID is increased. For example 4. record was duplicate so ID's like 1,2,3,5,6... Can i prevent this so how?|||

LacOniC:

Out of subject and not so important but i wondered. I set C as Unique. When i insert record, if it's duplicate so rollback transaction but ID is increased. For example 4. record was duplicate so ID's like 1,2,3,5,6... Can i prevent this so how?


You can't prevent this if you are using an Identity column, sorry. The only way to really prevent it is to have your own ID number table, read the next available value out of it, and assign that to your new record. All of that should be wrapped in the transaction.