Databases
StackOverflow2013
Postsdbo
POAdd a rownumber based on the sequence of values provided
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAdd a rownumber based on the sequence of values provided
    primarykey
    data
    text
    <pre><code>SELECT Code, Value FROM dbo.Sample </code></pre> <p>Output:</p> <pre><code> Code Value Alpha Pig Beta Horse Charlie Dog Delta Cat Echo Fish </code></pre> <p>I want to add a sequence column by specifying a list of Codes and sort the list based on the order specified in the IN clause.</p> <pre><code>SELECT Code, Value FROM dbo.Sample WHERE Code in ('Beta', 'Echo', 'Alpha') </code></pre> <p>I could declare a variable at the top to specify the Codes if that is easier.</p> <p>The key is that I want to add the row number based on the order that I specify them in.</p> <p>Output:</p> <pre><code> Row Code Value 1 Beta Horse 2 Echo Fish 3 Alpha Pig </code></pre> <hr> <p>Edit: I realized after that my Codes are all a fixed length which makes a big difference in how it could be done. I marked the answer below as correct, but my solution is to use a comma-separated string of values:</p> <pre><code>DECLARE @CodeList TABLE (Seq int, Code nchar(3)) DECLARE @CodeSequence varchar(255) DECLARE @ThisCode char(3) DECLARE @Codes int SET @Codes = 0 -- string of comma-separated codes SET @CodeSequence = 'ZZZ,ABC,FGH,YYY,BBB,CCC' ----loop through and create index and populate @CodeList WHILE @Codes*4 &lt; LEN(@CodeSequence) BEGIN SET @ThisCode = SUBSTRING(@CodeSequence,@Codes*4+1,3) SET @Codes = @Codes + 1 INSERT @CodeList (Seq, Code) VALUES (@Codes, @ThisCode) END SELECT Seq, Code from @CodeList </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USkevro
    1. USkevro
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAdd a rownumber based on the sequence of values provided
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POAdd a rownumber based on the sequence of values provided
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POAdd a rownumber based on the sequence of values provided
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthat select won't necessarily produce those values in that order without an ORDER BY
      singulars
      1. POAdd a rownumber based on the sequence of values provided
      1. USMitch Wheat
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload