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: Thursday, April 17, 2014 5:09 AM
Points: 156, Visits: 661
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: Today @ 2:22 AM
Points: 2,422, Visits: 7,436
SELECT a.Shift+b.Shift
FROM [dbo].[Mas_Shift] a
CROSS JOIN [dbo].[Mas_Shift] b
WHERE a.ShiftId < b.ShiftId;




Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1396619
Posted Friday, December 14, 2012 7:27 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
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?


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1396852
Posted Monday, December 17, 2012 9:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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 Monday, December 17, 2012 11:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
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: "..Database users may cause the system to generate or delete a surrogate, but they have no control over its value, nor is its value ever displayed to them ..."(Dr. Codd in ACM TODS, pp 409-410) and Codd, E. (1979), Extending the database relational model to capture more meaning. ACM Transactions on Database Systems, 4(4). pp. 397-434.

This means that a surrogate ought to act like an index; created by the user, managed by the system and NEVER seen by a user. That means never used in queries, DRI or anything else that a user does.

Codd also wrote the following:

"There are three difficulties in employing user-controlled keys as permanent surrogates for entities.

(1) The actual values of user-controlled keys are determined by users and must therefore be subject to change by them (e.g. if two companies merge, the two employee databases might be combined with the result that some or all of the serial numbers might be changed.).

(2) Two relations may have user-controlled keys defined on distinct domains (e.g. one uses social security, while the other uses employee serial numbers) and yet the entities denoted are the same.

(3) It may be necessary to carry information about an entity either before it has been assigned a user-controlled key value or after it has ceased to have one (e.g. and applicant for a job and a retiree).

These difficulties have the important consequence that an equi-join on common key values may not yield the same result as a join on common entities. A solution - proposed in part [4] and more fully in [14] - is to introduce entity domains which contain system-assigned surrogates. Database users may cause the system to generate or delete a surrogate, but they have no control over its value, nor is its value ever displayed to them....." (Codd in ACM TODS, pp 409-410).

References

Codd, E. (1979), Extending the database relational model to capture more meaning. ACM Transactions on Database Systems, 4(4). pp. 397-434

The steps for finding a key are

1) Look for an industry standard and the trusted external source that maintains and verifies it. I count this as a natural key, but you could argue that it is artificial.

2) Look for a natural key in the attributes. Example: (longitude, latitude) makes a good key for a geographical location. A GPS can be used to verify it.

3) If you must design a new identifier, plan it carefully -- especially if people will see and use it. You have to be able to verify it in application programs, so you should have a regular expression, other syntax rules, check digits. You have to be able to be verify in the reality of the model or with a trusted source that you maintain.

Validation means the format is good -- "This could one of our invoice numbers because it is 7 digits long, passes a Bull code check digit and begins with { '01', '02', '07', '99'}"

Verification means that it references a real entity -- "This is a real invoice because I can look it up in Accounts Payable and trace its approval back to Cindy Lu Who on 2005-02-12."

This is hard work. I have a few chapters in SQL PROGRAMMING STYLE on scales, measurements and how to design encoding schemes.

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!


Yes, they do! Sequential files (tape and card decks) evolved into sequential disk storage (ever work with ISAM and other indexing methods?). The ROWID in Oracle is based on the cylinder and track of a record on a disk drive. The ANSI/ISO Standard cursor model is taken directly from an IBM tape file system! We discussed this in ANSI X3H2 and the reason was simple. We needed to be able to plug SQL into COBOL programs without changing the applications.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1397369
Posted Tuesday, December 18, 2012 2:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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