Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BATTY_11
Starting Member
2 Posts |
 Posted - 2015-01-07 : 12:51:47
|
| Hi hope someone can help.I am trying to create a view on a database table where the query I am using returns only one row. I would like a column name to be the word "Date" plus the value returned.the column name is dbo.ActivityTable.Activity thus I want to do something like Select Activity AS (Date + dbo.activitytable.activity)from dbo.ActivityTable.Activitywhere ActivityType = 1Obviously this is not working but I don't know what syntax to use to achieve the desired column name. Any assistance gladly appreciated.Steve"Old fart trying to learn SQL" |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-01-07 : 12:56:58
|
| You will need to use dynamic SQL to make the returned column name be dynamic like that. Please note that this is highly not recommended and should be handled in the application layer instead.Please read this in its entirety to understand the CURSE and BLESSINGS of dynamic SQL: http://www.sommarskog.se/dynamic_sql.html. As a DBA, I focus on the CURSE, but there are legitimate uses of dynamic SQL that can improve performance. It's a topic Kimberly Tripp presents at conferences. Your query would not be improved by making it dynamic.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
BATTY_11
Starting Member
2 Posts |
Posted - 2015-01-08 : 10:28:30
|
| Thank you for the response Tara. My SQL is in it's early days. I normally use VS to get such results but the data is just too large.I am looking at an eventlog table for workflows. The key column is the workflowID and every time a step is taken in the workflow a new row is added with the activity name and action date (amongst others). I am trying to create data that shows time lapse between steps. I have achieved this but only with the use of several views of views of views and by creating unions etc. It is very customised and at risk that when changes are made it all falls down. Thus my current goal is to try and get things to default rather than custom coding.I will take your advice and read the article/s. I am also attempting to use the Pivot option so will give that a go as well.Steve"Old fart trying to learn SQL" |
|
|
|
|
|
|
View Column Names