I need a way to generate the Clinical Identifier column in Microsoft SQL Server.
I would prefer not to use a CTE.
The issue is only the "Clinicial Size Type" value of "OTHER" needs to be grouped.
The way you can tell a new "OTHER" grouping is starting, is "EN" is always the first language supplied.
| Clinical Size Type(s) | Clinical Size Type Description | Clinical Size Type Description Language | Clinical Identifier |
|---|---|---|---|
| Diameter (outer) | *BLANK | *BLANK | 1 |
| Angle | *BLANK | *BLANK | 2 |
| OTHER | Usable length | EN | 3 |
| OTHER | Distal flexible length | EN | 4 |
| OTHER | Tip Flexibility | EN | 4 |
| OTHER | Embout souple | FR | 4 |
| OTHER | Flexibilität der Spitze | DE | 4 |
| OTHER | Flessibilità della punta | IT | 4 |
| OTHER | Flexibilidade da ponta | PT | 4 |
| OTHER | Tipflexibiliteit | NL | 4 |
| OTHER | Flexibilidad de la punta | ES | 4 |
| OTHER | Truck | EN | 5 |
| OTHER | Truck FR | FR | 5 |
| OTHER | Truck DE | DE | 5 |
I need a way to generate the Clinical Identifier column in Microsoft SQL Server.
I would prefer not to use a CTE.
The issue is only the "Clinicial Size Type" value of "OTHER" needs to be grouped.
The way you can tell a new "OTHER" grouping is starting, is "EN" is always the first language supplied.
| Clinical Size Type(s) | Clinical Size Type Description | Clinical Size Type Description Language | Clinical Identifier |
|---|---|---|---|
| Diameter (outer) | *BLANK | *BLANK | 1 |
| Angle | *BLANK | *BLANK | 2 |
| OTHER | Usable length | EN | 3 |
| OTHER | Distal flexible length | EN | 4 |
| OTHER | Tip Flexibility | EN | 4 |
| OTHER | Embout souple | FR | 4 |
| OTHER | Flexibilität der Spitze | DE | 4 |
| OTHER | Flessibilità della punta | IT | 4 |
| OTHER | Flexibilidade da ponta | PT | 4 |
| OTHER | Tipflexibiliteit | NL | 4 |
| OTHER | Flexibilidad de la punta | ES | 4 |
| OTHER | Truck | EN | 5 |
| OTHER | Truck FR | FR | 5 |
| OTHER | Truck DE | DE | 5 |
- No one here ever want see a screenshot of your sample data. It's effectively worthless to us, and a good way for your question to get downvoted and ignored with no answer. We need formatted text we can copy/paste to somewhere like dbfiddle. Otherwise, you may not get any help here. – Joel Coehoorn Commented Mar 18 at 21:20
- 1 What denotes the order of your data at the moment? I don't see anything that denotes that, so how do you know what the "first" row is? – Thom A Commented Mar 18 at 21:32
- Is this all the data you have? If so there is no way to determine the groups. Remember that there is no guaranteed order to data being returned from a table unless you specify it. Also, just reading it, why would "Tip Flexibility" have an ID of 4 as the row above, also "EN" has the same ID. Unless you can somehow guarantee the order of the data from some column we can't see, or, there is some other common element between the related rows, I think this is not possible. – Alan Schofield Commented Mar 18 at 21:40
- 1 Why would you not want to use a CTE? Not that I think it will help, but why exclude it straight off the bat? That's like being a builder but refusing to use a hammer – Dale K Commented Mar 18 at 22:40
2 Answers
Reset to default 3So the Clinical Identifier column doesn't currently exist, and you want to generate it to match the data shown?
Not possible.
Your results depend on the records having an inherent order, which is something that doesn't exist.
For example, you need all the "Truck" rows grouped together at the end with the EN row first as a delimiter, then FR and DE... but there is nothing in the data we can sort by to ensure this happens.
You could run a simple SELECT * FROM [table] query and get the order this time, but the next time the rows might have a completely different order, even for the same query looking at the same table in the same database!
There is no such thing as table order, insert order, natural order, etc. If order matters, you MUST be able to specify that order from the data via an ORDER BY clause.
However, this will be possible if (and only if!) there are other column(s) we can look at to determine the base record order: an ID, Datetime, group number, etc. In that case, it's a gaps and islands problem that may also use the dense_rank() window function.
I need to create an identifier column when importing the file into the database.
Then this is the solution:
https://dbfiddle.uk/wCjhjdGZ