Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

concatenate of two values Expand / Collapse
Author
Message
Posted Friday, December 14, 2012 6:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 16, 2012 9:28 PM
Points: 15, Visits: 167
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


Post #1396615
Posted Friday, December 14, 2012 6:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 11, 2015 7:38 AM
Points: 164, Visits: 719
Can you describe what you are trying to do here?
Post #1396617
Posted Friday, December 14, 2012 6:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 26, 2015 7:10 AM
Points: 2,468, Visits: 8,053
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
Post #1396619
Posted Monday, December 17, 2012 9:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 24, 2015 9:31 AM
Points: 2,926, Visits: 5,410
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1397314
Posted Monday, December 17, 2012 9:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 24, 2015 9:31 AM
Points: 2,926, Visits: 5,410
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1397315
Posted Tuesday, December 18, 2012 2:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 24, 2015 9:31 AM
Points: 2,926, Visits: 5,410
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...


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

How to post your question to get the best and quick help
Post #1397635
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse