October 1, 2003 at 9:28 am
Hi,
I would like to display the field reffering column by its position.
For example, my table ABC has 12 columns - col1, col2,....col12, one for each month. Depending on a month calculated previously I'd like to select all rows in the corresponding column:
Let's mo=MONTH('09/05/2003'), so we should
select col9 from ABC
and it should select values of col9 for all rows. For Example:
Table ABC looks
CREATE table Abc (col1 int, col2 int, .....col12 int)
INSERT INTO Abc values (2,3,4,5,6,7,8,9,10,11,12,13)
INSERT INTO Abc values (3,4,5,6,7,8,9,10,11,12,13,14)
the result to select col9 should be
10
11
Thanks
Edited by - sql_gebe on 10/01/2003 12:40:42 PM
October 1, 2003 at 9:55 am
hi, try :-
create table ABC (col1 int,
col2 int, col3 int, col4 int, col5 int, col6 int, col7 int, col8 int, col9 int, col10 int, col11 int, col12 int)
insert into ABC values (1, 2, 3,4,5,6,7,8,9,10,11, 12)
declare @mo int, @sqlstring nvarchar(4000)
select @mo=MONTH('12/12/2003')
select @sqlstring = "select col" + convert(varchar(2), @mo) + " from ABC"
execute sp_executesql @sqlstring
should return :-
9
HTH
Paul
October 1, 2003 at 10:08 am
DECLARE @mo tinyint, @sql varchar(8000)
SET @mo = MONTH(GETDATE())
SET @sql = 'SELECT ptdCol' + STR(@mo, 2) + '
FROM Abc'
EXEC(@sql)
DECLARE @mo tinyint, @sql varchar(8000)
SET @mo = MONTH(GETDATE())
SELECT CASE @mo
WHEN 1 THEN Col1
WHEN 2 THEN Col2
WHEN 3 THEN Col3
WHEN 4 THEN Col4
WHEN 5 THEN Col5
WHEN 6 THEN Col6
WHEN 7 THEN Col7
WHEN 8 THEN Col8
WHEN 9 THEN Col9
WHEN 10 THEN Col10
WHEN 11 THEN Col11
WHEN 12 THEN Col12
END
FROM Abc
DECLARE @mo tinyint, @sql varchar(8000)
SET @mo = MONTH(GETDATE())
SELECT Col
FROM (
SELECT 1 pd, Col1 Col
FROM Abc
UNION ALL
SELECT 2, Col2
FROM Abc
UNION ALL
SELECT 3, Col3
FROM Abc
UNION ALL
SELECT 4, Col4
FROM Abc
UNION ALL
SELECT 5, Col5
FROM Abc
UNION ALL
SELECT 6, Col6
FROM Abc
UNION ALL
SELECT 7, Col7
FROM Abc
UNION ALL
SELECT 8, Col8
FROM Abc
UNION ALL
SELECT 9, Col9
FROM Abc
UNION ALL
SELECT 10, Col10
FROM Abc
UNION ALL
SELECT 11, Col11
FROM Abc
UNION ALL
SELECT 12, Col12
FROM Abc) n
WHERE pd = @mo
The last method denormalizes the data the way the schema should heve been denormalized to first normal form.
--Jonathan
--Jonathan
October 1, 2003 at 11:49 am
SELECT (SELECT 'COL' + (CAST(DATEPART(MONTH,GETDATE())) AS CHAR(2))))
FROM yourtablename
-SQLBill
October 2, 2003 at 1:38 am
SQLBill,
this works for you?? I tried it several times (with various changes), and I always get an error... which is about what I expected, because I already tried something very similar some time ago and didn't succeed. If there is a way how to make such a simple statement work, it would be nice.
October 2, 2003 at 7:53 am
I finally was able to set up a test and it's not working. It does concatenate the COL to the number, but I can't get it to use it as a column name. It's only being used as a string so it returns COL10.
I'll keep trying. BTW-there was a typo in that. It should have been ....GETDATE()) AS ...
-SQLBill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply