Query help urgent

  • Its urgent...

    Hi i want make a query such like there is a there is a 3 not null value between column img1 to img10 the query shud give me the count 3 ...

    if there is 1 not null value thn 1

    eg ...this table

    datatype for img col is image

    there are 3 not null rows so the count shud be 3 and

    there are 4 not null rows so the count shud be 2

    col1col1col1img1img2img3img4img5img6img7imp8img9img10

    abcabcabcnullvaluevaluevaluenullnullnullnullnullnull

    abcabcabcnullnullnullnullnullnullnullvaluevaluevalue

    abcabcabcnullnullnullnullnullnullvaluevaluevaluevalue

    abcabcabcvaluevaluevaluevaluenullnullnullnullnullnull

    abcabcabcnullnullnullnullvaluevaluevaluenullnullnull

  • waiting for the reply

  • It would really be helpful if, in the future, you would pose such questions with a properly formed create table statement and some ready to use data. Please see the link in my signature on how to do that easily. 😉

    Here's some code I did for a previous example that does what you want. Details are in the comments.

    drop table dbo.ZipPhone

    drop table #myhead

    go

    --===== Create the test table (NOT part of the solution)

    CREATE TABLE dbo.ZipPhone

    (

    ZipCode VARCHAR(6),

    Phone1 DECIMAL(10,0),

    Phone2 DECIMAL(10,0),

    Phone3 DECIMAL(10,0),

    Phone4 DECIMAL(10,0),

    Phone5 DECIMAL(10,0),

    Phone6 DECIMAL(10,0),

    Phone7 DECIMAL(10,0),

    Phone8 DECIMAL(10,0),

    Phone9 DECIMAL(10,0),

    Phone10 DECIMAL(10,0)

    )

    --===== Populate the test table with the data given in the post (NOT part of the solution)

    INSERT INTO dbo.ZipPhone

    (ZipCode,Phone1,Phone2,Phone3,Phone4,Phone5,Phone6,Phone7,Phone8,Phone9,Phone10)

    SELECT 12345,null,1111111111,2222222222,3333333333,4444444444,555555555,6666666666,7777777777,8888888888,999999999 UNION ALL

    SELECT 12346,null,null,2222222222,3333333333,4444444444,555555555,6666666666,7777777777,8888888888,999999999 UNION ALL

    SELECT 12347,null,1111111111,2222222222,3333333333,null,555555555,6666666666,7777777777,8888888888,999999999 UNION ALL

    SELECT 12348,null,null,2222222222,null,4444444444,555555555,6666666666,7777777777,8888888888,999999999

    --===== SOLUTION STARTS HERE! =====

    --===== Create a working table to hold normalized data for future denormalization

    CREATE TABLE #MyHead

    (ZipCode VARCHAR(6) NOT NULL,

    Phone DECIMAL(10) NOT NULL,

    OldCol TINYINT NOT NULL,

    NewCol TINYINT NOT NULL)

    --===== "Normalize" the data using an "old fashioned" method for "unpivoting" data

    -- eliminating NULLs in the process AND keeping track of the "old" phone column number.

    -- Using ">0" is a speed trick that's just a wee bit faster than IS NOT NULL.

    INSERT INTO #MyHead

    (ZipCode, Phone, OldCol, NewCol)

    SELECT ZipCode, Phone1 ,1 ,0 FROM dbo.ZipPhone WHERE Phone1 > 0 UNION ALL

    SELECT ZipCode, Phone2 ,2 ,0 FROM dbo.ZipPhone WHERE Phone2 > 0 UNION ALL

    SELECT ZipCode, Phone3 ,3 ,0 FROM dbo.ZipPhone WHERE Phone3 > 0 UNION ALL

    SELECT ZipCode, Phone4 ,4 ,0 FROM dbo.ZipPhone WHERE Phone4 > 0 UNION ALL

    SELECT ZipCode, Phone5 ,5 ,0 FROM dbo.ZipPhone WHERE Phone5 > 0 UNION ALL

    SELECT ZipCode, Phone6 ,6 ,0 FROM dbo.ZipPhone WHERE Phone6 > 0 UNION ALL

    SELECT ZipCode, Phone7 ,7 ,0 FROM dbo.ZipPhone WHERE Phone7 > 0 UNION ALL

    SELECT ZipCode, Phone8 ,8 ,0 FROM dbo.ZipPhone WHERE Phone8 > 0 UNION ALL

    SELECT ZipCode, Phone9 ,9 ,0 FROM dbo.ZipPhone WHERE Phone9 > 0 UNION ALL

    SELECT ZipCode, Phone10,10,0 FROM dbo.ZipPhone WHERE Phone10 > 0

    --===== Here's the trick... we need this clustered index to support the

    -- ranking we're going to do while maintaining the original order

    -- of the phone numbers based on the "old" phone column number.

    ALTER TABLE #MyHead

    ADD PRIMARY KEY CLUSTERED (ZipCode,OldCol)

    --===== Ok... all setup to do a "quirky update" to do some ranking...

    -- Declare the variables we need to do the actual ranking.

    -- The names of the variables pretty much tell all.

    DECLARE @PrevZipCode VARCHAR(6)

    DECLARE @PrevNewCol TINYINT

    SET @PrevZipCode = ''

    SET @PrevNewCol = 0

    --===== Do the "quirky update" that will do "Ordinal Ranking" of the phone numbers

    -- according to their original relative position to each other in the original data.

    -- In other words, we're doing this to preserve the order in which the original

    -- phone numbers appear in the original data, but vertically instead of horizontally.

    -- Also, if you just wanted a "normalized" table, you could stop here.

    -- Note that this avoids the hugely expensive "triangular join" method.

    UPDATE #MyHead

    SET @PrevNewCol = NewCol = CASE WHEN ZipCode = @PrevZipCode THEN @PrevNewCol + 1 ELSE 1 END,

    @PrevZipCode = ZipCode

    FROM #MyHead WITH(INDEX(0)) --Forces the clustered index to be scanned in order for the update

    --Note that this ONLY works on these "quirky updates" and cannot

    --be depended on for SELECT's.

    --===== This just shows what is meant by "Ordinal Ranking.

    -- Look at the "NewCol" to see what I mean.

    -- This is just for demonstration and is NOT part of the solution.

    SELECT *

    FROM #MyHead

    ORDER BY ZipCode,NewCol

    --===== Ok, we're all set... product a result set that meets the requirement of shifting

    -- all the phone numbers to the left, in their original order, so that all NULLs

    -- end up on the far right.

    -- This is what you call a "CrossTab" or "Pivot" and can be found in Books Online

    SELECT ZipCode,

    MAX(CASE WHEN NewCol = 1 THEN Phone END) AS Phone1,

    MAX(CASE WHEN NewCol = 2 THEN Phone END) AS Phone2,

    MAX(CASE WHEN NewCol = 3 THEN Phone END) AS Phone3,

    MAX(CASE WHEN NewCol = 4 THEN Phone END) AS Phone4,

    MAX(CASE WHEN NewCol = 5 THEN Phone END) AS Phone5,

    MAX(CASE WHEN NewCol = 6 THEN Phone END) AS Phone6,

    MAX(CASE WHEN NewCol = 7 THEN Phone END) AS Phone7,

    MAX(CASE WHEN NewCol = 8 THEN Phone END) AS Phone8,

    MAX(CASE WHEN NewCol = 9 THEN Phone END) AS Phone9,

    MAX(CASE WHEN NewCol = 10 THEN Phone END) AS Phone10

    FROM #MyHead

    GROUP BY ZipCode

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jeff thkz for the reply but i dnt want this.....

    i want the count

  • Unfortunately, we aren't mind readers. The help you get is only as good as the information you provide with your question.

    I suggest you read the article in Jeff's signature line, as it provides excellent instructions on how best to ask for assistance.

    The more information you provide, the better we can help you.

    😎

  • samsql (9/30/2008)


    jeff thkz for the reply but i dnt want this.....

    i want the count

    Yeah... You'e right. I misread your requirements. Sorry.

    If you could provide a little test data like I suggest in the article in the link in my signature, we can probably figure something out pretty quick.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • samsql (9/30/2008)


    Its urgent...

    Hi i want make a query such like there is a there is a 3 not null value between column img1 to img10 the query shud give me the count 3 ...

    if there is 1 not null value thn 1

    eg ...this table

    datatype for img col is image

    there are 3 not null rows so the count shud be 3 and

    there are 4 not null rows so the count shud be 2

    col1col1col1img1img2img3img4img5img6img7imp8img9img10

    abcabcabcnullvaluevaluevaluenullnullnullnullnullnull

    abcabcabcnullnullnullnullnullnullnullvaluevaluevalue

    abcabcabcnullnullnullnullnullnullvaluevaluevaluevalue

    abcabcabcvaluevaluevaluevaluenullnullnullnullnullnull

    abcabcabcnullnullnullnullvaluevaluevaluenullnullnull

    Sorry, I really wanted to help you, but for few minutes I just read you question over and over again, and I didn’t understand what you want. As I started reading the answers that you got, I realize that I’m not the only one that doesn’t understand what you want. Any reason that you ignore all the requests for reposting your question but this time ask the proper way (post DDL, insert statements and explain what you want)? Doing this would really improve you chances of getting an answer.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Actaul table contains diffrent datatype for image1 to image10 it is image datatype bcoz it contains images

    CREATE TABLE dbo.table2

    (

    col1 VARCHAR(10),

    image1 VARCHAR(10),

    image2 VARCHAR(10),

    image3 VARCHAR(10),

    image4 VARCHAR(10),

    image5 VARCHAR(10),

    image6 VARCHAR(10),

    image7 VARCHAR(10),

    image8 VARCHAR(10),

    image9 VARCHAR(10),

    image10 VARCHAR(10),

    col2 VARCHAR(10))

    ---- the test table with the data given

    INSERT INTO dbo.table2

    (col1,image1,image2,image3,image4,image5,image6,image7,image8,image9,image10,col2)

    SELECT 12345,null,null,null,null,null,null,null,'notnull','notnull','notnull','abc' UNION ALL

    SELECT 12346,'notnull',null,'notnull',null,null,null,null,null,null,'notnull','abc' UNION ALL

    SELECT 12347,null,null,null,'notnull','notnull',null,null,null,'notnull',null,'abc' UNION ALL

    SELECT 12348,null,null,'notnull',null,'notnull','notnull',null,'notnull',null,null,'abc' UNION ALL

    SELECT 12348,null,null,'notnull',null,'notnull','notnull',null,null,null,'notnull','abc'

    12345NULLNULLNULLNULLNULLNULLNULLnotnullnotnullnotnullabc

    12346notnullNULLnotnullNULLNULLNULLNULLNULLNULLnotnullabc

    12347NULLNULLNULLnotnullnotnullNULLNULLNULLnotnullNULLabc

    12348NULLNULLnotnullNULLnotnullnotnullNULLnotnullNULLNULLabc

    12348NULLNULLnotnullNULLnotnullnotnullNULLNULLNULLnotnullabc

    i want a query which gives me output like this ....

    select count(1) from table2 -- output shud be 3

    for 3 rows which contain 3 not null values and

    select count(1) from table2 -- output shud be 2

    for 2 rows which contain 4 not null values

  • jeff waiting for the reply

    how many rows are there with 3 not null values and

    how many rows are there with 4 not null values

    from column image1 to image10

  • Try this

    Select Cnt as Count1 from (

    Select 10 - (Img1 + Img2 + Img3 + Img4 + Img5 + Img6 + Img7 + Img7 + Img8 + Img9 + Img10) as Cnt from (

    Select Case IsNull([image1], 1) When 1 Then 1 Else 0 End Img1,

    Case IsNull([image2], 1) When 1 Then 1 Else 0 End Img2,

    Case IsNull([image3], 1) When 1 Then 1 Else 0 End Img3,

    Case IsNull([image4], 1) When 1 Then 1 Else 0 End Img4,

    Case IsNull([image5], 1) When 1 Then 1 Else 0 End Img5,

    Case IsNull([image6], 1) When 1 Then 1 Else 0 End Img6,

    Case IsNull([image7], 1) When 1 Then 1 Else 0 End Img7,

    Case IsNull([image8], 1) When 1 Then 1 Else 0 End Img8,

    Case IsNull([image9], 1) When 1 Then 1 Else 0 End Img9,

    Case IsNull([image10], 1) When 1 Then 1 Else 0 End Img10 from table2) C) P

    Group by Cnt

  • nope this is not the case...

  • any one plz reply ... need the query

  • This may help you.

    -- *** Test Data **

    DECLARE @t TABLE

    (

    &nbsp&nbsp&nbsp&nbspCol1 int NOT NULL

    &nbsp&nbsp&nbsp&nbsp,Image1 varchar(10) NULL

    &nbsp&nbsp&nbsp&nbsp,Image2 varchar(10) NULL

    &nbsp&nbsp&nbsp&nbsp,Image3 varchar(10) NULL

    &nbsp&nbsp&nbsp&nbsp,Image4 varchar(10) NULL

    &nbsp&nbsp&nbsp&nbsp,Image5 varchar(10) NULL

    &nbsp&nbsp&nbsp&nbsp,Image6 varchar(10) NULL

    &nbsp&nbsp&nbsp&nbsp,Image7 varchar(10) NULL

    &nbsp&nbsp&nbsp&nbsp,Image8 varchar(10) NULL

    &nbsp&nbsp&nbsp&nbsp,Image9 varchar(10) NULL

    &nbsp&nbsp&nbsp&nbsp,Image10 varchar(10) NULL

    )

    INSERT INTO @t

    SELECT 12345,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'notnull','notnull','notnull' UNION ALL

    SELECT 12346,'notnull',NULL,'notnull',NULL,NULL,NULL,NULL,NULL,NULL,'notnull' UNION ALL

    SELECT 12347,NULL,NULL,NULL,'notnull','notnull',NULL,NULL,NULL,'notnull',NULL UNION ALL

    SELECT 12348,NULL,NULL,'notnull',NULL,'notnull','notnull',NULL,'notnull',NULL,NULL UNION ALL

    -- Assuming Col1 is meant to be PK so made this 12349

    SELECT 12349,NULL,NULL,'notnull',NULL,'notnull','notnull',NULL,NULL,NULL,'notnull'

    -- *** End Test Data ***

    SELECT COUNT(*) AS RowsWith

    &nbsp&nbsp&nbsp&nbsp,NotNulls

    FROM

    (

    &nbsp&nbsp&nbsp&nbspSELECT Col1, COUNT(NImage) As NotNulls

    &nbsp&nbsp&nbsp&nbspFROM

    &nbsp&nbsp&nbsp&nbsp(

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT T.Col1

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,CASE N.N

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN 1 THEN Image1

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN 2 THEN Image2

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN 3 THEN Image3

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN 4 THEN Image4

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN 5 THEN Image5

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN 6 THEN Image6

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN 7 THEN Image7

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN 8 THEN Image8

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN 9 THEN Image9

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN 10 THEN Image10

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspEND AS NImage

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFROM @t T

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspCROSS JOIN

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp(

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT 1 UNION ALL

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT 2 UNION ALL

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT 3 UNION ALL

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT 4 UNION ALL

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT 5 UNION ALL

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT 6 UNION ALL

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT 7 UNION ALL

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT 8 UNION ALL

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT 9 UNION ALL

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT 10

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp) N (N) -- or use number/tally table

    &nbsp&nbsp&nbsp&nbsp) D

    &nbsp&nbsp&nbsp&nbspGROUP BY Col1

    ) V

    GROUP BY NotNulls

  • ABOVE QUERY IS GIVING CONVERTION ERROR, WITH SLIGHT CHANGES DONE.

    Select Cnt as Count1 from

    (

    Select 10 - (CAST(Img1 AS INT)+ CAST(Img2 AS INT) + CAST(Img3 AS INT) + CAST(Img4 AS INT) + CAST(Img5 AS INT) +

    CAST(Img6 AS INT) + CAST(Img7 AS INT)+ CAST(Img7 AS INT) + CAST(Img8 AS INT)+ CAST(Img9 AS INT) + CAST(Img10 AS INT)) as Cnt from

    (

    Select Case CAST(IsNull([image1], 1) AS CHAR) When '1' Then '1' Else '0' End Img1,

    Case CAST(IsNull([image2], 1) AS CHAR) When '1' Then '1' Else '0' End Img2,

    Case CAST(IsNull([image3], 1) AS CHAR) When '1' Then '1' Else '0' End Img3,

    Case CAST(IsNull([image4], 1) AS CHAR) When '1' Then '1' Else '0' End Img4,

    Case CAST(IsNull([image5], 1) AS CHAR) When '1' Then '1' Else '0' End Img5,

    Case CAST(IsNull([image6], 1) AS CHAR) When '1' Then '1' Else '0' End Img6,

    Case CAST(IsNull([image7], 1) AS CHAR) When '1' Then '1' Else '0' End Img7,

    Case CAST(IsNull([image8], 1) AS CHAR) When '1' Then '1' Else '0' End Img8,

    Case CAST(IsNull([image9], 1) AS CHAR) When '1' Then '1' Else '0' End Img9,

    Case CAST(IsNull([image10], 1) AS CHAR) When '1' Then '1' Else '0' End Img10 from table2

    )C

    ) P

    Group by Cnt ;

  • samsql (10/1/2008)


    jeff waiting for the reply

    how many rows are there with 3 not null values and

    how many rows are there with 4 not null values

    from column image1 to image10

    Actually, most of my original answer was correct for what you need... The Select with all the unions in it that eliminates the nulls is the first step... then you just need to COUNT the data base on the row key.

    DECLARE @t TABLE

    (

    Col1 int NOT NULL

    ,Image1 varchar(10) NULL

    ,Image2 varchar(10) NULL

    ,Image3 varchar(10) NULL

    ,Image4 varchar(10) NULL

    ,Image5 varchar(10) NULL

    ,Image6 varchar(10) NULL

    ,Image7 varchar(10) NULL

    ,Image8 varchar(10) NULL

    ,Image9 varchar(10) NULL

    ,Image10 varchar(10) NULL

    )

    INSERT INTO @t

    SELECT 12345,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'notnull','notnull','notnull' UNION ALL

    SELECT 12346,'notnull',NULL,'notnull',NULL,NULL,NULL,NULL,NULL,NULL,'notnull' UNION ALL

    SELECT 12347,NULL,NULL,NULL,'notnull','notnull',NULL,NULL,NULL,'notnull',NULL UNION ALL

    SELECT 12348,NULL,NULL,'notnull',NULL,'notnull','notnull',NULL,'notnull',NULL,NULL UNION ALL

    -- Assuming Col1 is meant to be PK so made this 12349

    SELECT 12349,NULL,NULL,'notnull',NULL,'notnull','notnull',NULL,NULL,NULL,'notnull'

    -- *** End Test Data ***

    SELECT Col1, SUM(ImagePresent) AS Images

    FROM (--=== Derived table "d" counts each non-null image value

    SELECT Col1, 1 AS ImagePresent FROM @t WHERE Image1 IS NOT NULL UNION ALL

    SELECT Col1, 1 FROM @t WHERE Image2 IS NOT NULL UNION ALL

    SELECT Col1, 1 FROM @t WHERE Image3 IS NOT NULL UNION ALL

    SELECT Col1, 1 FROM @t WHERE Image4 IS NOT NULL UNION ALL

    SELECT Col1, 1 FROM @t WHERE Image5 IS NOT NULL UNION ALL

    SELECT Col1, 1 FROM @t WHERE Image6 IS NOT NULL UNION ALL

    SELECT Col1, 1 FROM @t WHERE Image7 IS NOT NULL UNION ALL

    SELECT Col1, 1 FROM @t WHERE Image8 IS NOT NULL UNION ALL

    SELECT Col1, 1 FROM @t WHERE Image9 IS NOT NULL UNION ALL

    SELECT Col1, 1 FROM @t WHERE Image10 IS NOT NULL)d

    GROUP BY Col1

    I imagine you can figure out the rest... think of using the whole code above as yet another derived table to do your counts of counts. 😉

    By the way... the table as you have it is in pretty bad shape and totally denormalized. The result of the "derived" table in my code above is what this table should actually look like. If you need an image sequence number, you could certainly add that.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply