Tag: pivot

Select matching rows as columns

I think it would be quickest to show by example, so here goes a simplified version of my problem. I am using SQL Server. This is the data model SURVEY QUESTION ANSWER —— ——– ——- surveyId questionId answerId questionText surveyId questionId answerText Here’s some sample data SURVEY QUESTION ———- ———— ————– | surveyId | | […]

SQL Server : Pivot and unpivots

I’m trying to pivot this SQL Server table, however I’m struggling to wrap my head around what I need to do. I can do a basic pivot, however this has become very challenging. I am in essence to convert the table below to Company Code Account Class Period Billings Collections Debtors ——————————————————————————– 500 Accounts Receiveable […]

doing a simple pivot on year

I have a table: +—-+——-+——+ | id | times | year | +—-+——-+——+ | 5 | 2 | 2008 | | 6 | 76 | 2008 | | 2 | 43 | 2009 | | 4 | 5 | 2009 | | 1 | 3 | 2010 | | 9 | 6 | 2010 | […]

How to do a PIVOT by day cycle in 31 days in month in SQL Server

I have this table: CREATE TABLE [dbo].[tblProducts] ( [CATEGORY_ID] [nvarchar](50) NULL, [PRODUCT] [nvarchar](50) NULL, [PRODUCTION_DATE] [datetime] NULL ) ON [PRIMARY] I need to display total products of every day in month Days should be pivoted CATE 1 2 3 4 5 6 7…..30 (or 31) –>days of month CATE1 1 5 –> count product by […]

pivot table to make each date as column with out any aggregate column

I have following query which give me a row for each day earning for each employee. Now i want to show those date rows as columns . my current query and its output is as follow. declare @StartDate datetime,@EndDate datetime,@CompanyId int set @StartDate=’01/01/2013′ set @EndDate=’01/31/2013′ set @CompanyId=3 ;with d(date) as ( select cast(@StartDate as datetime) […]

tsql row to column (pivot)

i have a table with the following information CREATE TABLE [dbo].[HR_DEPENDENTS]( [PARENT_ID] [bigint] NOT NULL, [DEPENDENT_ID] [bigint] NOT NULL, [LAST_NAME] [varchar](100) NOT NULL, [FIRST_NAME] [varchar](100) NULL, [DATE_OF_BIRTH] [date] NULL) insert into HR_DEPENDENTS (PARENT_ID, DEPENDENT_ID, LAST_NAME, first_name, date_of_birth) values (100, 1, ‘Ray’, ‘First Child’,cast(’06/01/2001′ as date)) insert into HR_DEPENDENTS (PARENT_ID, DEPENDENT_ID, LAST_NAME, first_name, date_of_birth) values (100, […]

Rows in their own columns depending on their date and symbolized by 'x'

Dear All, please help me since I’m newbie in SQL Server. I have a select query that currently produces the following results: DoctorName Team Visit date dr. As A 5 dr. Sc A 4 dr. Gh B 6 dr. Nd C 31 dr As A 7 Using the following query: SELECT d.DoctorName, t.TeamName, ca.VisitDate FROM […]

how can i use LRTIM and RTRIM to find invalid records and insert it into error table?

I have a query that finds the invalid records from each column in a table. but the length of the data type is not same for all the columns. I am saying this because when I use LTRIM and RTRIM , it gives an error “The type of column “SubscriberLastName” conflicts with the type of […]

Date compare within Pivot

I am still working on getting the data out exactly as I want it and it is almost there – one last question. Here is my code: DECLARE @QA1 TABLE (SID varchar(7), FormID varchar(max), DateExam date, Present varchar(3)) INSERT INTO @QA1 VALUES(1, ‘Form1’, ‘20110101’, ‘Yes’) INSERT INTO @QA1 VALUES(2, ‘Form1’, ‘20110201’, ‘Yes’) INSERT INTO @QA1 […]

how can i sql pivot this select?

SELECT sc.CID,sc.CodeName as OverviewText,scRAG.CodeName as RAGStatusText FROM StatusCode sc LEFT OUTER JOIN ProjectOverview po ON sc.CID = po.ProjectOverviewCID AND po.ProjectId = 180 LEFT OUTER JOIN StatusCode scRAG ON po.RAGStatusCID = scRAG.CID WHERE sc.SCID = 18 the above code result sin this: CID OverviewText RAGStatusText 153 Cost Green 154 Requirements Yellow 155 Schedule NULL 156 Technical […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.