Need help on T-SQL. May be Pivot Tables
-
I've tables and data as following,
GO
/****** Object: Table [dbo].[CV] Script Date: 04/15/2014 00:49:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CV](
[CVID] [int] NOT NULL,
[Name] [varchar](100) NULL,
CONSTRAINT [PK_CV] PRIMARY KEY CLUSTERED
(
[CVID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[CV] ([CVID], [Name]) VALUES (68, N'Ahmad Jaezan Hj Ramly')
INSERT [dbo].[CV] ([CVID], [Name]) VALUES (69, N'MUHAMMAD FIRDAUS BIN SHAARI')
INSERT [dbo].[CV] ([CVID], [Name]) VALUES (70, N'nadia bt abdul aziz')
INSERT [dbo].[CV] ([CVID], [Name]) VALUES (71, N'SITI HAJAR BT MOHAMAD ARIS @ AZIZ')
INSERT [dbo].[CV] ([CVID], [Name]) VALUES (72, N'JAMILUDIN BIN JENON')
INSERT [dbo].[CV] ([CVID], [Name]) VALUES (76, N'MUHAMAD FERID BIN SARJO')
INSERT [dbo].[CV] ([CVID], [Name]) VALUES (77, N'Lim Jinn An')
/****** Object: Table [dbo].[DeclarationResponse] Script Date: 04/15/2014 00:49:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DeclarationResponse](
[DeclarationResponseID] [int] NOT NULL,
[CVID] [int] NOT NULL,
[DeclarationQuestionID] [int] NULL,
[Response] [text] NULL,
[Answer] [bit] NULL,
CONSTRAINT [PK_DeclarationResponse] PRIMARY KEY NONCLUSTERED
(
[DeclarationResponseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [DeclarationResponse_UQ1] UNIQUE NONCLUSTERED
(
[CVID] ASC,
[DeclarationQuestionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (185, 72, 1, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (187, 72, 3, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (189, 72, 5, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (191, 72, 7, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (193, 77, 1, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (195, 77, 3, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (197, 77, 5, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (199, 77, 7, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (186, 72, 2, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (188, 72, 4, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (190, 72, 6, N'', 1)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (192, 72, 8, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (194, 77, 2, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (196, 77, 4, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (198, 77, 6, N'', 1)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (200, 77, 8, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2025, 76, 1, N'At Ilsas for 5 time', 1)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2027, 76, 3, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2029, 76, 5, N'Technician position', 1)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2031, 76, 7, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2026, 76, 2, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2028, 76, 4, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2030, 76, 6, N'', 1)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2032, 76, 8, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (279741, 76, 9, N'', 0)
/****** Object: ForeignKey [FK_DeclarationResponse_CV] Script Date: 04/15/2014 00:49:42 ******/
ALTER TABLE [dbo].[DeclarationResponse] WITH CHECK ADD CONSTRAINT [FK_DeclarationResponse_CV] FOREIGN KEY([CVID])
REFERENCES [dbo].[CV] ([CVID])
GO
ALTER TABLE [dbo].[DeclarationResponse] CHECK CONSTRAINT [FK_DeclarationResponse_CV]
GO
To built column name and data, see below:-
1- Answer-Staff (9) is a DeclarationQuestionID=9 and Column is Answer
2- Response-Staff (9) is a DeclarationQuestionID=9 and Column is Response
3- Answer-Disease (2) is a DeclarationQuestionID=2 and Column is Answer
4- Response-Physical (2) is a DeclarationQuestionID=2 and Column is Response
The expected result as follow,
CVID | Answer-Staff (9) | Response-Staff Number (9) | Answer-Disease (2) | Response-Physical (2)
_____________________________________________________________________________________________________________
72 | null | null | 0 |
76 | 0 | | 0 |
77 | null | null | 0 |
/*null will be given if DeclarationQuestionID not exists*/
I'm stuck. Please help me
-
You did a nice job posting ddl and sample data. However, once you got to the output it went awry very quickly. You have things like "Answer-Staff" , "Response-Staff". What are those? What are the values supposed to be and why? In your sample data you have nothing but empty strings and a couple of actual values for response but in your expected outcome you have NULL, 0. Nowhere in your output are the two actual values from your sample data.
You seem to be missing almost the entirety of the explanation of your desired output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I knew my table design is silly.
To built column name and data, see below:-
1- Answer-Staff (9) is a DeclarationQuestionID=9 and Column is Answer
2- Response-Staff (9) is a DeclarationQuestionID=9 and Column is Response
3- Answer-Disease (2) is a DeclarationQuestionID=2 and Column is Answer
4- Response-Physical (2) is a DeclarationQuestionID=2 and Column is Response
I've no idea how is SQL looks like
-
Little Nick (4/14/2014)
I knew my table design is silly.To built column name and data, see below:-
1- Answer-Staff (9) is a DeclarationQuestionID=9 and Column is Answer
2- Response-Staff (9) is a DeclarationQuestionID=9 and Column is Response
3- Answer-Disease (2) is a DeclarationQuestionID=2 and Column is Answer
4- Response-Physical (2) is a DeclarationQuestionID=2 and Column is Response
I've no idea how is SQL looks like
I agree the design is a bit "left of center"...
Does something like this get you started?
select *
from CV
left join DeclarationResponse dr on dr.CVID = cv.CVID and dr.DeclarationQuestionID in (9, 2)
I am still kind of struggling with your expected output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
OK I think I got it. You are going to need a tally table for this because you have missing data that you want to report.
BTW, you should stop using the text datatype. It is a serious PITA to work with and has been deprecated since 2005. You should use the max datatypes instead.
I still don't really get what you are trying to do here but whatever, I leave the business rules to you.
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select Name,
MAX(case when N = 9 then cast(answer as varchar) end) as AnswerStaff,
MAX(case when N = 9 then cast(Response as varchar(max)) end) as ResponseStaffNumber,
MAX(case when N = 2 then cast(answer as varchar) end) as AnswerDisease,
MAX(case when N = 2 then cast(Response as varchar(max)) end) as ResponsePhysical
from CV
cross apply cteTally t
left join DeclarationResponse dr on dr.CVID = cv.CVID and dr.DeclarationQuestionID in (9, 2)
where t.N in (9, 2)
group by Name
There are two different things going on here. First is the tally table. You need to read and understand what that means and how they work. In this case we are using it as a numbers table to drive your query so you get rows with NULL when there is no data present. Read more about tally tables here. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
The next piece is a bit more complicated. It is using a cross tab query to kludge this data from its poor design into something somewhat more useful.
You can (and should) read about crosstabs by following the links in my signature. I would highly recommend you NOT use this code until you understand what it does and can explain it to someone else. It is your phone that will be ringing at 3am, not mine. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
For
select *from CV
left join DeclarationResponse dr on dr.CVID = cv.CVID and dr.DeclarationQuestionID in (9, 2)
This is the result
I only need to display record if DeclarationResponseID is not null. So, the CVID was 72, 76 and 77
See below,
1- I want to make it - the DeclarationQuestionID value is a column.
2- Based on DeclarationQuestionID value, the column will be 2 and 9
Based on CVID=72 and DeclarationQuestionID=2. So, Response='' and Answer=0.
Based on CVID=72, there is no DeclarationQuestionID=9. So, Response=null and Answer=null.
So, the result must be
CVID | Answer-Staff (9) | Response-Staff Number (9) | Answer-Disease (2) | Response-Physical (2)_____________________________________________________________________________________________________________
72 | null | null | 0 |
Based on CVID=76 and DeclarationQuestionID=2. So, Response='' and Answer=0.
Based on CVID=76 and DeclarationQuestionID=9. So, Response='' and Answer=0.
So, the result must be
CVID | Answer-Staff (9) | Response-Staff Number (9) | Answer-Disease (2) | Response-Physical (2)
_____________________________________________________________________________________________________________
76 | 0 | | 0 |
Based on CVID=77 and DeclarationQuestionID=2. So, Response='' and Answer=0.
Based on CVID=77, there is no DeclarationQuestionID=9. So, Response=null and Answer=null.
So, the result must be
CVID | Answer-Staff (9) | Response-Staff Number (9) | Answer-Disease (2) | Response-Physical (2)_____________________________________________________________________________________________________________
77 | null | null | 0 |
The value = null must be given, if CVID do not have DeclarationQuestionID=2 nor DeclarationQuestionID=9
-
OK so you can drop the tally table portion of my previous post and simplify it to this.
select Name, cv.CVID,
MAX(case when dr.DeclarationQuestionID = 9 then cast(answer as varchar) end) as AnswerStaff,
MAX(case when dr.DeclarationQuestionID = 9 then cast(Response as varchar(max)) end) as ResponseStaffNumber,
MAX(case when dr.DeclarationQuestionID = 2 then cast(answer as varchar) end) as AnswerDisease,
MAX(case when dr.DeclarationQuestionID = 2 then cast(Response as varchar(max)) end) as ResponsePhysical
from CV
join DeclarationResponse dr on dr.CVID = cv.CVID and dr.DeclarationQuestionID in (9, 2)
group by Name, cv.CVID
order by cv.CVID
--edit--
Added CVID to the output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
YIPPIEEEEEEEEE. TQ SIR 😀
-
Little Nick (4/14/2014)
YIPPIEEEEEEEEE. TQ SIR 😀BUT...do you understand it? That by far is more important than the fact that it works. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply