CODEWITHSUNDEEP
sql-server
c-sharp
c-sharp

Reputation: 593

How can we add a column on the fly in a dynamic table in SQL SERVER?

My question needs little explanation so I'd like to explain this way: I've got a table (lets call it RootTable), it has one million records, and not in any proper order. What I'm trying to do is to get number of rows(@ParamCount) from RootTable and at the same time these records must be sorted and also have an additional column(with unique data) added on the fly to maintain a key for row identification which will be used later in the program. It can take any number of parameters but my basic parameters are the two which mentioned below. It's needed for SQL SERVER environment.

e.g.

RootTable    
ColumnA       ColumnB    ColumnC
ABC           city       cellnumber
ZZC           city1      cellnumber     
BCD           city2      cellnumber
BCC           city3      cellnumber

Passing number of rows to return @ParamCount and columnA startswith @paramNameStartsWith

<b>@paramCount:2 <br>
@ParamNameStartsWith:BC</b>

desired result:

Id(added on the fly)   ColumnA       ColumnB      ColumnC
101                    BCC          city3        cellnumber
102                    BCD          city2         cellnumber

Here's another point about Id column. Id must maintain its order, like in the above result it's starting from 101 because 100 is already assigned to the first row when sorted and added column on the fly, and because it starts with "ABC" so obviously it won't be in the result set.

Any kind of help would be appreciated.

NOTE: My question title might not reflect my requirement, but I couldn't get any other title.

Upvotes: 0

Views: 1559

Answers (1)

Martin K.
Martin K.

Reputation: 1060

So first you need your on-the-fly-ID. This one is created by the ROW_NUMBER() function which is available from SQL Server 2005 onwards. What ROW_NUMBER() will do is pretty self-explaining i think. However it works only on a partition. The Partition is specified by the OVER clause. If you include GROUP BY within the OVER clause, you will have multiple partitions. In your case, there is only one partition which is the whole table, therefor GROUP BY is not necessary. However an ORDER BY is required so that the system knows which record should get which row number in the partition. The query you get is:

SELECT ROW_NUMBER() OVER (ORDER BY ColumnA) ID, ColumnA,ColumnB,ColumnC
FROM RootTable

Now you have a row number for your whole table. You cannot include any condition like your @ParamNameStartsWith parameter here because you wanted a row number set for the whole table. The query above has to be a subquery which provides the set on which the condition can be applied. I use a CTE here, i think that is better for readability:

;WITH OrderedList AS (
    SELECT ROW_NUMBER() OVER (ORDER BY ColumnA) ID, ColumnA,ColumnB,ColumnC
    FROM RootTable
)
SELECT *
FROM OrderedList
WHERE ColumnA LIKE @ParamNameStartsWith+'%'

Please note that i added the wildcard % after the parameter, so that the condition is basically "starts with" @ParamNameStartsWith.

Finally,if i got you right you wanted only @ParamCount rows. You can use your parameter directly with the TOP keyword which is also only possible with SQL Server 2005 or later.

;WITH OrderedList AS (
    SELECT ROW_NUMBER() OVER (ORDER BY ColumnA) ID, ColumnA,ColumnB,ColumnC
    FROM RootTable
)
SELECT TOP (@ParamCount) *
FROM OrderedList
WHERE ColumnA LIKE @ParamNameStartsWith+'%'

Upvotes: 1

Related Questions