Is this possible?? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Is this possible??

Author

Topic

vinothrao84
Starting Member

21 Posts

Posted - 2008-10-21 : 23:31:22

Hi,

I have a system that auto repeat a column in a table once reach every 4 column. So when my query returns let say 19 rows, then my system will show 5 rows and total 19 columns. On the last row, the system will only show 3 column cause its less than 4 column. Is the possible a SQL statement to show NULL value on last row if its less than every 4 rows.
Example: If my query reach only 2 row, i want the SQL to show 4 rows with the other 2 remains as NULL. If it returns 5 rows, then i want the SQL to show 8 rows with the other remains as NULL.

Its very complicated, but i not sure how to accomplished this.

Pls help.

Thx.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-22 : 06:03:29

Hi vinothrao84,

Please post some sample data and indicate exactly how you need the outupt to look. At the moment I can't get my head around exactly what you mean. Someone will be able to help you after you post the sample data and expected results I am sure.

-------------
Charlie

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-10-22 : 09:10:59

You may have to use a temp table or table variable:

-- *** Test Data ***
DECLARE @YourTable TABLE
(
    YourColumn varchar(20) COLLATE DATABASE_DEFAULT NOT NULL
)
INSERT INTO @YourTable
SELECT 'One' UNION ALL
SELECT 'Two' UNION ALL
SELECT 'Three' UNION ALL
SELECT 'Four' UNION ALL
SELECT 'Five' 
-- -- *** End Test Data ***

DECLARE @t TABLE
(
    -- Use your column's datatype
    YourColumn varchar(20) COLLATE DATABASE_DEFAULT NULL
)
DECLARE @Rows int

INSERT INTO @t
SELECT YourColumn
FROM @YourTable

SET @Rows = @@ROWCOUNT

INSERT INTO @t
SELECT CAST(NULL AS varchar(20)) -- or whatever your datatype
FROM
(
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4
) N (N)
WHERE @Rows > 0
    AND N.N <= 4 - @Rows%4

SELECT *
FROM @t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources