CODEWITHSUNDEEP
sqlgroup-by
Gaurav
Gaurav

Reputation: 597

how to query to get desired data

here below is mt database table

CREATE TABLE [dbo].[mtblLog_Book](
[Logbook_Number] [int] IDENTITY(1,1) NOT NULL,
[Sno] [int] NULL,
[Vehicle_Number] [nvarchar](100) NULL,
[Vehicle_Booking_Date] [datetime] NULL,
[Time_From] [datetime] NULL,
[Time_To] [datetime] NULL,
[KM_Start] [int] NULL,
[KM_End] [int] NULL,
[Total] [int] NULL,
[Vehicle_Used_By] [varchar](100) NULL,
[Cost_Code] [nvarchar](50) NULL,
[Budget_Line] [nvarchar](50) NULL,
[Entry_Date] [datetime] NULL
) ON [PRIMARY]

there are repeated Cost_Code. I need a query so that I can get a data in the below format

CostCode1
......................
All data which belong to CostCode1
......................

CostCode2
......................
All data which belong to CostCode2
......................

CostCode3
......................
All data which belong to CostCode3
......................

Cont.... to CostCode*n*

Thanks

Upvotes: 0

Views: 76

Answers (2)

DividesByZero
DividesByZero

Reputation: 83

If you want to display like the way you have shown above.thn use this code

    protected void show()
    {
     Response.Clear();
     string sql = "select Cost_Code from mtblLog_Book";
     ds = obj.openDataset(sql, schoolCode);

     if (ds.Tables[0].Rows.Count == 0)
     {
        Response.Write("[{\"Records\":\"" + "NA" + "\"}]");
     }
     else
     {
        int i;
        string output;
        for (i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
        {
        string sqlOutput ="Select * from mtblLog_Book where CostCode='"+ds.Tables[0].Rows[i][ "CostCode"].ToString()+ "';";
            Dataset dsOutPut= new Dataset;
          output = "[{\" Logbook_Number \":\"" +dsOutPut.Tables[0].Rows[i]["Logbook_Number"].ToString() + "\",\" Sno\": \"" + dsOutPut.Tables[0].Rows[i][" Sno"] + "\",\" Vehicle_Number\": \"" + dsOutPut.Tables[0].Rows[i][" Vehicle_Number"].ToString() + "\",\" Vehicle_Booking_Date\": \"" + dsOutPut.Tables[0].Rows[i][" Vehicle_Booking_Date"].ToString() + "\" }]"

            Response.Write(output);
            if (i < dsOutPut.Tables[0].Rows.Count - 1)
            {
                Response.Write("\n");
            }
        }
      }
    }

Upvotes: 1

T I
T I

Reputation: 9933

If you are using sql server and SSMS you could set it to output results to text and do something like

DECLARE @costCode NVARCHAR(50)

DECLARE cur CURSOR FOR
SELECT DISTINCT Cost_Code
FROM dbo.mtblLog_Book

OPEN cur
FETCH NEXT FROM cur INTO @costCode
WHILE @@FETCH_STATUS=0
BEGIN
    PRINT @costCode
    PRINT '......................................'
    SELECT * FROM dbo.mtblLog_Book
    WHERE Cost_Code = @costCode
    PRINT '......................................'
              PRINT ''  
END

CLOSE cur
DEALLOCATE cur

Then copy and paste the result to wherever you need it

Upvotes: 0

Related Questions