T-SQL Problem

Question

I have following table (SQL Server) Table name is LandParcels

Blockid   ParcelNo  Stateorprivate
========================
11001901   30       Deemana
11001901   35       Deemana
11001901   41       State
11001901   45       State
11001901   110      Private
11001901   111      Private

11001902   1        Deemana
11001902   11       State
11001902   16       Private

11002001   15       Deemana
11002001   16       State
11003001   20       Private
11002003   2        Deemana
11002003   3        State
11003003   4        Private

Blockid (Numeric) = first 6 digits used for Cadastral Map No and last 2 digits for the Block No

eg: 110019 is Cadastal map no and 01 is Block No.

I used the following query

select substring(ltrim(str(blockid)),1,6) as blockid,stateorprivate, count(*) as noofLP from LandParcels group by blockid, stateorprivate order by blockid asc

Result is

Blockid  Stateorprivate  noofLP
========================
110019   Deemana         2
110019   State           2
110019   Private         2
110019   Deemana         1
110019   State           1
110019   Private         1
110020   Deemana         1
110020   State           1
110020   Private         1
110020   Deemana         1
110020   State           1
110020   Private         1

I want to get the following result for a report

blockid  noofBlocks   Deemana   State  Private  Amt_of_Deemana_State_Private
110019    2            3          3       3          9  
110020    2            2          2       2          6

How to query this. Pl help me.

Answer 1

It looks like you want to to use Pivot - there are heaps of questions out there on stack overflow about them.

Here's the overview provided by MSDN

http://msdn.microsoft.com/en-us/library/ms177410.aspx

-- Original answer from before the target result was updated follows

I think you need to include the Substring in the group by like

select substring(ltrim(str(blockid)),1,6) as ReportBlockId,
    count(*) as noofLP     
from LandParcels 
group by substring(ltrim(str(blockid)),1,6), stateorprivate 
order by blockid asc

Previously you were grouping on the whole BlockDd value, which would give a separate result for each row because of the difference introduced by the BlockNo portion of the string.

Answer 2

You start your query:

select substring(ltrim(str(blockid)),1,6) as blockid

which immediately gives the DB an ambiguity -- in the rest of the query, does blockid stand for the original column of that name, or does it stand for this homonymous one?

don't do that -- it's absurd to overload a DB engine with even more ambiguity than it already had to deal with; use as myblockid or whatever here, and myblockid in the rest of the query when that is what you mean. This may not solve every problem, but it will make your life, the DB engine's, AND that of anybody trying to help you out, much less of a nightmare.

Answer 3

I'm not going to check if this works, but you should look at using sum and case.

select
substring(ltrim(str(blockid)),1,6) as blockid,
sum(case stateorprivate when 'Deemana' then 1 else 0 end) as Deemana,
sum(case stateorprivate when 'State' then 1 else 0 end) as State,
sum(case stateorprivate when 'Private' then 1 else 0 end) as Private,
count(*) as Amt_of_Deemana_State_Private
from LandParcels group by blockid 
order by blockid asc

Answer 4

You could do something like this:

SELECT 
    SUBSTRING(LTRIM(STR(Blockid)), 1, 6) AS blockid,
    COUNT(DISTINCT SUBSTRING(LTRIM(STR(Blockid)), 7, 2)) AS noofBlocks,
    SUM(CASE Stateorprivate WHEN 'Deemana' THEN 1 ELSE 0 END) AS Deemana,
    SUM(CASE Stateorprivate WHEN 'State' THEN 1 ELSE 0 END) AS [State],
    SUM(CASE Stateorprivate WHEN 'Private' THEN 1 ELSE 0 END) AS [Private],
    SUM(CASE Stateorprivate
  WHEN 'Deemana' THEN 1
  WHEN 'State' THEN 1
  WHEN 'Private' THEN 1
  ELSE 0
 END) AS Amt_of_Deemana_State_Private
FROM LandParcels
GROUP BY SUBSTRING(LTRIM(STR(Blockid)), 1, 6)

However, if the database schema is under your control, you should consider normalization.

Answer 5

Something like this?

SELECT substring(ltrim(str(lp.blockid)),1,6) as blockid, 
 w.noofBlocks
 x.Deemana,
 y.State,
 z.Private,
 COUNT(*) AS Amt_of_Deemana_State_Private
FROM LandParcels lp
 INNER JOIN (
  SELECT substring(ltrim(str(lp.blockid)),1,6) as myblockid, COUNT(*) AS Deemana
  FROM LandParcels lp2
  WHERE Stateorprivate = 'Deemana'
 ) x ON (substring(ltrim(str(lp.blockid)),1,6) = x.myblockid)
 INNER JOIN (
  SELECT substring(ltrim(str(lp.blockid)),1,6) as myblockid, COUNT(*) AS State
  FROM LandParcels lp3
  WHERE Stateorprivate = 'State'
 ) y ON (substring(ltrim(str(lp.blockid)),1,6) = y.myblockid)
 INNER JOIN (
  SELECT substring(ltrim(str(lp.blockid)),1,6) as myblockid, COUNT(*) AS Private
  FROM LandParcels lp4
  WHERE Stateorprivate = 'Private'
 ) z ON (substring(ltrim(str(lp.blockid)),1,6) = z.myblockid)
 CROSS JOIN (
  SELECT COUNT(DISTINCT substring(ltrim(str(lp.blockid)),1,6) as myblockid)
  FROM LandParcels lp5
 ) w
GROUP BY substring(ltrim(str(lp.blockid)),1,6)

Answer 6

I believe that this query will achieve your desired results, except that the NoOfBlocks field is the first column instead of the second column. I also used CadastalMapNo for the result set column name instead of blockid on the suggestion from Alex Martelli that it added ambiguity because there was already something else named blockid. The reason that I put the NoOfBlocks field as the first column is because I believe that SQLServer requires the count function to be the first field in the select list when the distinct keyword is used.

I haven't actually tested this and it could have poor performance, but I'm pretty sure it's correct as I understand the question.

    SELECT
        COUNT(DISTINCT SUBSTRING(LTRIM(STR(blockid)),7,8)) as NoOfBlocks,
        SUBSTRING(LTRIM(STR(blockid)),1,6) as CadastalMapNo, 
        (CASE WHEN Stateorprivate='Deemana' then 1 else 0 end) as Deemana,
        (CASE WHEN Stateorprivate='State' then 1 else 0 end) as State,
        (CASE WHEN Stateorprivate='Private' then 1 else 0 end) as Private,
        COUNT(*) as Amt_of_Deemana_State_Private 
    FROM 
        LandParcels
    GROUP BY 
        CadastalMapNo
    ORDER BY
        CadastalMapNo