tsql: how to add a key column in query results ?

hi! I'm practicing tsql.

I want to create a query the spits out results that I can export to csv/excel.

On the fly, for the results i want to insert the first column, with a numeric key. However I do not want this column inserted into the real, live database table or sql... just in the results. any tips?
1
2
3

so lets say...
pull all columns, from the table... during the query add a column 1, that will become a key for the export.

exported results:
1, jeremy, company
2, blah, blah

Something like this?

hmm thats a good idea but i dont think i was clear in my initial posting.

one table of mixed date:
query1: grabbing rows for "type=cars"
query2: grabbing rows for "type=buses"
query3: grabbing rows for "type=bikes"

so basically i want to grab all the columns and rows where type=cars, export to a csv with a key starting at #1.

table1:
query1, for cars... gives all the rows where the type column = cars.
-HOWEVER: column 1 will be inserted with keys starting 1-x


i could easily export the entire database, sort by type and copy and paste the data into smaller csv. however that wouldn't teach me as much. thanks for your feedback!

I can't tell if you're alluding to a simple GROUP BY clause, a PIVOT table, or some other aggregation.

Please post the database product you're using (with version number), a table schema, some row INSERT statements, and an actual example of what you're looking for in the output using the data from the INSERT statements.

scenario:
a. create a select statement to grab all rows from a table, where the column value is true.
-return the results of the query, for only the rows where the column value is true
ie. type=cars

b. in the selected query results, insert a column for keys numbered 1 to x.
1
2
3
....

jroe52 said:

scenario:
a. create a select statement to grab all rows from a table, where the column value is true.
-return the results of the query, for only the rows where the column value is true
ie. type=cars

b. in the selected query results, insert a column for keys numbered 1 to x.
1
2
3
....

Click to expand...

So like adding a WHERE clause to my first example? I'm not sure I'm following.

jroe52 said:

scenario:
a. create a select statement to grab all rows from a table, where the column value is true.
-return the results of the query, for only the rows where the column value is true
ie. type=cars

b. in the selected query results, insert a column for keys numbered 1 to x.
1
2
3
....

Click to expand...

You did not follow PTNL's request for more details, so it's very difficult to help you effectively. Your example export data in your original post is not accurate at all since you did not provide the actual expected data you want to export. (you left off the "key" column in your "sample" exported data)

Now, since your OP is so fuzzy and it sounds like you didn't even execute Sgraffite's proposed solution, here's what his solution will output:


And if you added a WHERE clause, you would have:


SELECT ROW_NUMBER() OVER(ORDER BY UserID) AS RowNumber, UserName, Type FROM Users WHERE Type='Car';

Then your result would be:




Perhaps you just want a new auto-generated key placed into a new column - but each time a new "type" is encountered, reset the generated key back to 1?

this is simple:

SELECT ROW_NUMBER() OVER(PARTITION BY Type, ORDER BY Description) AS RowNumber, Type, Description FROM Vehicles;

Now your results look like this;

Thank you so much! I am working with sensitive info so I had to be vague. This gives me the context clues I was looking for, thank you!

Just to be safe: it won't actually insert the key column into the table... but in the query results correct?

nm this worked perfect and didn't modify the table. thanks again