SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


concatenate of two values


concatenate of two values

Author
Message
SWATH
SWATH
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 171
Hi


i have table like




CREATE TABLE [dbo].[Mas_Shift]
(
[ShiftId] [int] IDENTITY(1,1) NOT NULL,
[FacilityId] [int] NULL,
[ShiftType] [nvarchar](5) NULL,
[Shift] [nvarchar](3) NULL
)

INSERT [dbo].[Mas_Shift] ([ShiftId], [FacilityId], [ShiftType], [Shift]) VALUES (4, 1, N'3', N'M')
INSERT [dbo].[Mas_Shift] ([ShiftId], [FacilityId], [ShiftType], [Shift]) VALUES (5, 1, N'3', N'N')
INSERT [dbo].[Mas_Shift] ([ShiftId], [FacilityId], [ShiftType], [Shift]) VALUES (6, 1, N'1', N'G')




i need o/p like

shift
---------
MN
NG
MG



THANK YOU
-------------
SWAT
_simon_
_simon_
Mr or Mrs. 500
Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)

Group: General Forum Members
Points: 593 Visits: 776
Can you describe what you are trying to do here?
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9160 Visits: 8492
SELECT a.Shift+b.Shift
FROM [dbo].[Mas_Shift] a
CROSS JOIN [dbo].[Mas_Shift] b
WHERE a.ShiftId < b.ShiftId;




Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12606 Visits: 5478
CELKO (12/14/2012)
I hope you know that IDENTITY has no place in RDBMS. It is how non-SQL programmers fake a magnetic tape file in SQL. You also do not know about ISO-11179 naming rules. Is this what you meant if you had a valid schema with a key?


CREATE TABLE MAS_Shifts
(facility_id INTEGER NOT NULL,
shift_name CHAR(3) NOT NULL,
PRIMARY KEY (facility_id, shift_name),
shift_type CHAR(5) NOT NULL);

INSERT INTO MAS_Shifts
VALUES (1, 'M', 3') , (1, 'N', '3'), ( 1, 'G', '1');

After that, I have no idea what your question is. Can you try again?


I hope you know better and will disregard the above misleading advise.
IDENTITY has a very important place in RDBMS and especially in SQL Server. It's very often the best candidate for a surrogate PK.
IDENTITY-like structures are implemented in most of the known RDBMS(s) and they have nothing to do with magnetic tape files or/and punch cards!

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12606 Visits: 5478
OP requirements very obscure.
What should happen if there is another group of data with different FacilityId:


INSERT [dbo].[Mas_Shift] ([ShiftId], [FacilityId], [ShiftType], [Shift]) VALUES (7, 2, N'3', N'A')
INSERT [dbo].[Mas_Shift] ([ShiftId], [FacilityId], [ShiftType], [Shift]) VALUES (8, 2, N'3', N'B')
INSERT [dbo].[Mas_Shift] ([ShiftId], [FacilityId], [ShiftType], [Shift]) VALUES (9, 2, N'1', N'C')



If FacilityId is a group denominator, then you can use the following:


SELECT a.Shift+b.Shift
FROM [dbo].[Mas_Shift] a
JOIN [dbo].[Mas_Shift] b
ON b.FacilityId = a.FacilityId
WHERE a.ShiftId < b.ShiftId;



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12606 Visits: 5478
CELKO (12/17/2012)
IDENTITY has a very important place in RDBMS and especially in SQL Server. It's very often the best candidate for a surrogate PK.


You might want to actually read Dr. Codd and any book on RDBMS. Please quote Dr. Codd on this topic in TODS, any presentation or any speech, show me where it is in his 12 rules, etc.

People get "exposed physical locator" and surrogate mixed up; they are totally different concepts. Let me start with an appeal to authority, with a quote from Dr. Codd:
...


To me, the above is like:
I'm telling you:
There is no God!

You telling me:
You might want to actually read the Bible...

and
Let me start with an appeal to authority, with a quote from Pope...


There are theories and there is a real life. And in the real life there are cases where you will have a great trouble finding Natural Keys. We had numerous discussion on this forum where I've tried to get from you an example of Natural Keys for very common database entities such as "Person" and "Company" and you didn't manage to advise anything useful (something which will work internationally for people and for all companies sizes).
But let try another one. Let say Vatican wants a database of all Christian saints. What kind of Natural Key will you advise? SSN number will not work, regardless of what Dr. Codd might written in his nice books...
Also, there are solutions where IDENTITY is a best candidate for PK anyway - data warehouses. Can you advise anything better than identity for slowly changing dimensions?
There are other aspects which make IDENTITY to be very good PK and performance one of them...

I have proven to you once my googling skills, haven't I? So, you do like authorities... :


From C. J. Date's book "An Introduction to Database Systems, C J Date, 8th Edition":

Page 434, reference 14.21 - he refers to P. Hall, J. Owlett and S J P Todd "Relations and Entities" and says:

"Surrogate Keys are keys in the usual Relational sense but have the following specific properties:

They always involve exactly one attribute.

Their values serve solely as surrogate (hence the name) for the entities they stand for ......

When a new entity is inserted into the database, it is given a surrogate key value that has never been used before and will never be used again, even if the entity in question is subsequently deleted.

Ideally surrogate keys value would be system-generated.

You took Dr. Codd's from the same site, didn't you. Actually, it was not there to explain why surrogate keys shouldn't be used, quite the opposite...

So looks like some other authorities do not have any problem with using Surrogates, especially system-generated ones. That SQL Server IDENTITY is for!

You stance on the IDENTITY, shows that you are very knowledgeable theoretic, but you don't have much real life experience with using SQL Server in enterprise level solutions.
Should I say that you know nothing about SQL Server, that you should take and read at least some book on it(not yours, of cause)? Microsoft SQL Server Book Online will do for starter...
Hehe

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search